[Home] [Help]
PACKAGE BODY: APPS.BIS_VIEW_GENERATOR_PVT
Source
1 PACKAGE BODY bis_view_generator_pvt AS
2 /* $Header: BISTBVGB.pls 120.2 2008/04/23 17:07:24 dbowles ship $ */
3
4 -- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
5 --- All rights reserved.
6 ---
7 --- FILENAME
8 ---
9 --- BISTBVGB.pls
10 ---
11 --- DESCRIPTION
12 ---
13 --- body of package which generates the business views
14 ---
15 --- NOTES
16 ---
17 --- HISTORY
18 ---
19 --- 29-JUL-98 surao created
20 --- 19-MAR-99 Edited by WNASRALL@US: for exception handling)
21 --- 21-Apr-99 Edited by WNASRALL@US: commented out various debug statements.
22 --- 18-NOV-99 Edited by DBOWLES@US: added check for p_debug before
23 --- DBMS_OUTPUT
24 --- 10-NOV-00 Edited by WNASRALL@US: added new function generate_pruned_view
25 --- 19-DEC-00 Edited by ILI@US: changed generate_pruned_view to obtain
26 --- segment list across database link
27 --- 20-DEC-00 Edited by WNASRALL@US: Added update generate status across
28 --- database link to generate_view
29 --- and generate_pruned_view
30 --- 26-DEC-00 Edited by WNASRALL@US: final debug of cross-database links
31 --- 02-FEB-00 Edited by WNASRALL@US: preparation for base table select
32 --- 06-APR-00 Edited by DBOWLES. Modified update_flexfields
33 --- procedure, update_View procedure passing parameter of
34 --- bis_vg_types.Flex_Column_Comment_Table_Type. Created new procedure
35 --- comment_Flex_Columns which is called after the view is generated.
36 --- This procedure will comment columns derived from flexfield
37 --- with data regarding specifics of the flexfield definition.
38 --- 11-DEC-01 Edited by DBOWLES Added dr driver comments.
39 --- 31-JAN-02 Fix bug 2208122 by phu
40 --- 10-MAY-02 Fix bug 2369734 by phu
41 --- 12-JAN-05 Fix bug 4093769 by amitgupt
42 --- 18-JAN-05 Modified by AMITGUPT for GSCC warnings
43 --============================================================================
44 -- CONSTANTS
45 --============================================================================
46
47 G_PKG_NAME CONSTANT VARCHAR2(60) :='BIS_VIEW_GENERATOR_PVT';
48
49 g_newline CONSTANT VARCHAR2(1):='
50 ';
51
52 g_tab CONSTANT VARCHAR2(1):=' ';
53
54 update_status_stmt CONSTANT VARCHAR2(320) :=
55 'UPDATE EDW_LOCAL_GENERATION_STATUS
56 SET generate_status = :status,
57 error_message = :error,
58 last_update_date = Sysdate
59 WHERE flex_view_name = :viewname'
60 ;
61
62 insert_status_stmt CONSTANT VARCHAR2(560) :=
63 'INSERT INTO EDW_LOCAL_GENERATION_STATUS
64 (FLEX_VIEW_NAME,
65 GENERATE_STATUS,
66 ERROR_MESSAGE,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN,
70 CREATED_BY,
71 CREATION_DATE)
72 values
73 ( :viewname,
74 :status,
75 :error,
76 sysdate,0,0,0,Sysdate
77 )'
78 ;
79
80 base_table_v_query_stmt CONSTANT VARCHAR2(2000) :=
81 'SELECT upper(table_name),
82 upper(table_alias),
83 upper(source_column_name)
84 FROM edw_view_gen_base_table_v@edw_apps_to_wh
85 WHERE
86 instance_code = :1
87 AND
88 flex_view_name = :2
89 ORDER BY table_name, table_alias'
90 ;
91
92 selection_v_query_stmt CONSTANT VARCHAR2(2000) :=
93 'SELECT
94 structure _num,
95 structure_name ,
96 application_column_name,
97 segment_name,
98 segment_datatype,
99 id_flex_code,
100 flex_field_type,
101 flex_field_name,
102 application_name,
103 FROM edw_view_gen_flex_v@edw_apps_to_wh
104 WHERE object_short_name = :1
105 AND instance_code = :2 '
106 ; --- For use in release 4.0
107
108 selection_query_stmt CONSTANT VARCHAR2(2000) :=
109 'SELECT
110 a.structure_num,
111 a.structure_name ,
112 a.application_column_name,
113 a.segment_name,
114 a.value_set_datatype,
115 a.id_flex_code,
116 decode(a.flex_field_type,''A'',''K'',a.flex_field_type),
117 a.flex_field_name,
118 c.application_short_name application_name
119 FROM edw_flex_seg_mappings@edw_apps_to_wh a,
120 edw_fact_flex_fk_maps@edw_apps_to_wh b,
121 fnd_application@edw_apps_to_wh c
122 WHERE b.fact_short_name = :1
123 AND b.enabled_flag =''Y''
124 AND b.dimension_short_name = a.dimension_short_name
125 AND a.instance_code = :2
126 AND c.application_id = a.application_id
127 union
128 select distinct
129 b.structure_num,
130 b.structure_name,
131 b.application_column_name,
132 b.segment_name,
133 b.value_set_datatype,
134 b.id_flex_code,
135 b.flex_field_type,
136 b.flex_field_name,
137 c.application_short_name application_name
138 from edw_attribute_mappings@edw_apps_to_wh a,
139 edw_flex_attribute_mappings@edw_apps_to_wh b,
140 fnd_application@edw_apps_to_wh c
141 where a.source_view = :3
142 and a.object_short_name= :1
143 and a.instance_code = :2
144 and a.ATTR_MAPPING_PK = b.attr_mapping_fk
145 AND c.application_id = b.application_id'
146 ;
147
148 -- Bug 6819715 New constants and global variables to control session settings related to the RDBMS optimizer
149 v_shared_pool CONSTANT VARCHAR2(50) := 'alter system flush shared_pool';
150 v_session_sort CONSTANT VARCHAR2(50) := 'alter session set "_newsort_enabled"=false';
151
152 ---
153 --============================================================================
154 -- mode for the program
155 --============================================================================
156 --
157 g_mode bis_vg_types.view_generator_mode_type := bis_vg_types.production_mode;
158 --
159
160
161 --=====================
162 --PRIVATE TYPES
163 --=====================
164 --- Weak REF CURSOR TYPE for use in dynamic queries
165 TYPE Ref_Cursor_Type IS REF CURSOR;
166
167 TYPE superset_rec_type IS
168 RECORD
169 ( table_name VARCHAR2(200)
170 , table_alias VARCHAR2(40)
171 , column_name VARCHAR2(60)
172 )
173 ;
174
175 TYPE superset_table_type
176 IS
177 TABLE of superset_rec_type;
178
179 TYPE superset_summary_rec_type is
180 record
181 ( table_name VARCHAR2(200)
182 , table_alias VARCHAR2(40)
183 , first_record NUMBER
184 , last_record NUMBER
185 , currently_valid BOOLEAN
186 )
187 ;
188
189 TYPE superset_summary_table_type
190 IS
191 TABLE of superset_summary_rec_type;
192
193 --=====================
194 --PRIVATE PROCEDURES
195 --=====================
196 --
197 -- ============================================================================
198 --FUNCTION : get_tag_keyword_position
199 --PARAMETERS: 1. p_view_table view table text
200 -- 2. p_string_set set of strings to look for
201 -- 3. p_start_pointer start pointer
202 -- 4. x_return_status error or normal
203 -- 5. x_error_Tbl table of error messages
204 --COMMENT : Call this function to get start position of any string in
205 -- p_string_set
206 --RETURN : view_character_pointer
207 --EXCEPTION : None
208 -- ============================================================================
209 FUNCTION get_tag_keyword_position
210 ( p_view_table IN bis_vg_types.View_Text_Table_Type
211 , p_string_set IN bis_vg_types.View_Text_Table_Type
212 , p_start_pointer IN bis_vg_types.View_Character_Pointer_Type
213 , x_return_status OUT VARCHAR2
214 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
215 )
216 RETURN bis_vg_types.view_character_pointer_type
217 IS
218 l_pointer bis_vg_types.View_Character_Pointer_Type;
219 l_save_pointer bis_vg_types.View_Character_Pointer_Type;
220 l_done BOOLEAN ;
221 l_char VARCHAR2(1);
222 BEGIN
223 l_done := FALSE;
224 bis_debug_pub.Add('> get_tag_keyword_position');
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226 l_pointer := p_start_pointer;
227 WHILE (NOT l_done) LOOP
228 l_pointer := bis_vg_parser.get_keyword_position
229 ( p_view_table
230 , p_string_set
231 , l_pointer
232 , x_return_status
233 , x_error_Tbl
234 );
235 IF (bis_vg_util.null_pointer( l_pointer
236 , x_return_status
237 , x_error_Tbl
238 ) = TRUE
239 ) THEN
240 l_done := TRUE;
241 ELSIF(l_pointer.col_num = 1) THEN
242 l_done := TRUE;
243 ELSE
244 l_save_pointer := bis_vg_util.decrement_pointer
245 ( p_view_table
246 , l_pointer
247 , x_return_status
248 , x_error_Tbl
249 );
250 l_char := bis_vg_util.get_char
251 ( p_view_table
252 , l_save_pointer
253 , x_return_status
254 , x_error_Tbl
255 );
256 IF(l_char = '''') THEN
257 l_done := TRUE;
258 ELSE
259 l_pointer := bis_vg_util.increment_pointer
260 ( p_view_table
261 , l_pointer
262 , x_return_status
263 , x_error_Tbl
264 );
265 END IF;
266 END IF;
267 END LOOP;
268 bis_debug_pub.Add('< get_tag_keyword_position');
269 RETURN l_pointer;
270 --
271 --
272 EXCEPTION
273 when FND_API.G_EXC_ERROR then
274 x_return_status := FND_API.G_RET_STS_ERROR ;
275 RAISE FND_API.G_EXC_ERROR;
276 when FND_API.G_EXC_UNEXPECTED_ERROR then
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 when others then
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281 BIS_VG_UTIL.Add_Error_Message
282 ( p_error_msg_id => SQLCODE
283 , p_error_description => SQLERRM
284 , p_error_proc_name => G_PKG_NAME||'.get_tag_keyword_position'
285 , p_error_table => x_error_tbl
286 , x_error_table => x_error_tbl
287 );
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 END get_tag_keyword_position;
290 --
291 -- ============================================================================
292 --PROCEDURE : Update_Flexfields
293 --- PARAMETERS:
294 --- 1. p_View_Column_Table table of varchars to hold columns of view text
295 --- 2. p_View_Select_Table table of varchars to hold select clause of view
296 --- 3. p_Mode mode of the program
297 --- 4. p_selected_columns IN BIS_VG_TYPES.flexfield_column_table_type:
298 --- Use only for calls from generate_pruned_view
299 --- 5. p_Column_Pointer pointer to the key flex column in column table
300 --- 6. p_Select_Pointer pointer to the select clause
301 --- 7. x_Column_Table table of varchars to hold additional columns
302 --- 8. x_Select_Table table of varchars to hold additional columns
303 --- 9. x_Column_Comment_Table table of records to hold
304 --- flex data for flex derived columns.
305 --- 10. x_Column_Pointer pointer to the character after the delimiter
306 --- (column table)
307 --- 11. x_Select_Pointer pointer to the character after the delimiter
308 --- (select table)
309 --- 12. x_return_status error or normal
310 --- 13. x_error_Tbl table of error messages
311 ---
312 --- COMMENT : Call this procedure to update the flex field pointed
313 --- EXCEPTION : None
314 --- ========================================================================
315
316 PROCEDURE update_flexfields -- PRIVATE PROCEDURE
317 ( p_view_column_text_table IN bis_vg_types.View_Text_Table_Type
318 , p_view_select_text_table IN bis_vg_types.View_Text_Table_Type
319 , p_mode IN NUMBER := bis_vg_types.sqlplus_production_mode
320 , p_selected_columns IN BIS_VG_TYPES.flexfield_column_table_type := NULL
321 , p_column_pointer IN bis_vg_types.view_character_pointer_type
322 , p_select_pointer IN bis_vg_types.view_character_pointer_type
323 , x_column_table OUT bis_vg_types.View_Text_Table_Type
324 , x_select_table OUT bis_vg_types.View_Text_Table_Type
325 , x_column_comment_table OUT bis_vg_types.Flex_Column_Comment_Table_Type
326 , x_column_pointer OUT bis_vg_types.view_character_pointer_type
327 , x_select_pointer OUT bis_vg_types.view_character_pointer_type
328 , x_return_status OUT VARCHAR2
329 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
330 )
331 IS
332 l_str VARCHAR2(300);
333 l_sel_str VARCHAR2(300);
334 l_pointer bis_vg_types.view_character_pointer_type;
335 l_col NUMBER;
336 BEGIN
337 --
338 bis_debug_pub.Add('> update_flexfields');
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340 --
341 --- --- --- DEBUG ---
342 --- bis_vg_util.print_view_pointer ( p_select_pointer
343 --- , x_return_status
344 --- , x_error_Tbl
345 --- );
346 l_str := bis_vg_util.get_row( p_view_select_text_table
347 , p_select_pointer
348 , x_return_status
349 , x_error_Tbl
350 );
351 bis_debug_pub.Add('l_str = '||l_str);
352 l_str := bis_vg_parser.get_string_token
353 ( l_str
354 , p_select_pointer.col_num
355 , ':'
356 , l_col
357 , x_return_status
358 , x_error_Tbl
359 );
360 l_sel_str := UPPER(l_str);
361 --
362 bis_debug_pub.Add('l_sel_str = '||l_sel_str);
363 l_str := bis_vg_util.get_row( p_view_column_text_table
364 , p_column_pointer
365 , x_return_status
366 , x_error_Tbl
367 );
368 --
369 l_str := bis_vg_parser.get_string_token
370 ( l_str
371 , p_column_pointer.col_num
372 , ':'
373 , l_col
374 , x_return_status
375 , x_error_Tbl
376 );
377 l_str := UPPER(l_str);
378 IF (l_col IS NULL) THEN
379 bis_debug_pub.Add('out pointer is null');
380 l_pointer := bis_vg_util.increment_pointer_by_row
381 ( p_view_column_text_table
382 , p_column_pointer
383 , x_return_status
384 , x_error_Tbl
385 );
386 ELSE
387 l_pointer := p_column_pointer;
388 l_pointer.col_num := l_col;
389 END IF;
390 --
391 IF (l_str <> l_sel_str) THEN
392
393 BIS_VG_UTIL.Add_Error_message
394 ( p_error_msg_name => MISMATCHED_TAG_EXCEPTION_MSG
395 , p_error_proc_name => G_PKG_NAME||'.update_flexfields'
396 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
397 , p_error_table => x_error_tbl
398 , x_error_table => x_error_tbl
399 );
400 RAISE FND_API.G_EXC_ERROR;
401
402 END IF;
403 ---
404 bis_debug_pub.Add('l_str = '||l_str);
405 IF (l_str = '_KF') THEN
406 BIS_VG_KEY_FLEX.add_Key_Flex_Info( p_View_Column_text_Table
407 , p_View_Select_Text_Table
408 , p_Mode
409 , p_selected_columns
410 , p_Column_Pointer
411 , p_Select_Pointer
412 , p_select_Pointer -- not used
413 , x_Column_Table
414 , x_Select_Table
415 , x_Column_Comment_Table
416 , x_Column_Pointer
417 , x_Select_Pointer
418 , x_return_status
419 , x_error_Tbl
420 );
421 --- --- --- DEBUG ----
422 --- bis_vg_util.print_View_Text
423 --- ( x_Column_Table
424 --- , x_return_status
425 --- , x_error_Tbl
426 --- );
427 --- bis_vg_util.print_View_Text
428 --- ( x_Select_Table
429 --- , x_return_status
430 --- , x_error_Tbl
431 --- );
432 ELSE
433 IF (l_str = '_DF') THEN
434 BIS_VG_DESC_FLEX.add_Desc_Flex_Info( p_View_Column_text_Table
435 , p_View_Select_Text_Table
436 , p_Mode
437 , p_selected_columns
438 , p_Column_Pointer
439 , p_Select_Pointer
440 , p_select_pointer -- not used
441 , x_Column_Table
442 , x_Select_Table
443 , x_Column_Comment_Table
444 , x_Column_Pointer
445 , x_Select_Pointer
446 , x_return_status
447 , x_error_Tbl
448 );
449 ELSE
450 IF (l_str = '_LA') THEN
451 bis_vg_lookup.add_Lookup_Info( p_View_Column_text_Table
452 , p_View_Select_Text_Table
453 , p_Mode
454 , p_Column_Pointer
455 , p_Select_Pointer
456 , x_Column_Table
457 , x_Select_Table
458 , x_Column_Pointer
459 , x_Select_Pointer
460 , x_return_status
461 , x_error_Tbl
462 );
463 ELSE
464 NULL;
465 -- raise exception
466 END IF;
467 END IF;
468 END IF;
469 bis_debug_pub.Add('< update_flexfields');
470 --
471 EXCEPTION
472 when FND_API.G_EXC_ERROR then
473 x_return_status := FND_API.G_RET_STS_ERROR ;
474 RAISE FND_API.G_EXC_ERROR;
475 when FND_API.G_EXC_UNEXPECTED_ERROR then
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 when others then
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
480 BIS_VG_UTIL.Add_Error_Message
481 ( p_error_msg_id => SQLCODE
482 , p_error_description => SQLERRM
483 , p_error_proc_name => G_PKG_NAME||'. update_flexfields'
484 , p_error_table => x_error_tbl
485 , x_error_table => x_error_tbl
486 );
487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
488 --
489 END update_flexfields;
490 --
491 -- ============================================================================
492 --FUNCTION : exclude_comma_before_tag
493 --PARAMETERS:
494 -- 1. p_View_Select_Text_Table contains select text
495 -- 2. p_Select_Pointer in pointer
496 -- 3. p_start_pointer pointer to beginning of select table
497 -- 4. x_Select_Pointer out pointer
498 -- 5. x_return_status error or normal
499 -- 6. x_error_Tbl table of error messages
500 --COMMENT : Call this procedure to position the pointer to handle the comma
501 -- when in remove_tags_mode or when _DF with no segments encountered
502 --Return : 1 iff found a comma else 0
503 --EXCEPTION : None
504 -- ============================================================================
505 FUNCTION exclude_comma_before_tag -- PRIVATE function
506 ( p_View_Select_Text_Table IN bis_vg_types.View_Text_Table_Type
507 , p_Select_Pointer IN bis_vg_types.view_character_pointer_type
508 , p_start_pointer IN bis_vg_types.view_character_pointer_type
509 , x_Select_Pointer OUT bis_vg_types.view_character_pointer_type
510 , x_return_status OUT VARCHAR2
511 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
512 )
513 RETURN NUMBER
514 IS
515 --
516 l_select_pointer bis_vg_types.view_character_pointer_type;
517 --
518 BEGIN
519 bis_debug_pub.Add('> exclude_comma');
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521 l_select_pointer := p_Select_Pointer;
522 WHILE( bis_vg_util.get_char( p_View_Select_Text_Table
523 , l_select_pointer
524 , x_return_status
525 , x_error_Tbl
526 )
527 <> ','
528 ) LOOP
529 l_select_pointer := bis_vg_util.decrement_pointer
530 ( p_View_Select_Text_Table
531 , l_select_pointer
532 , x_return_status
533 , x_error_Tbl
534 );
535 IF(bis_vg_util.equal_pointers
536 ( l_select_pointer
537 , p_start_pointer
538 , x_return_status
539 , x_error_Tbl
540 )
541 )
542 THEN
543 x_select_pointer := p_select_pointer;
544 bis_debug_pub.Add('< exclude_comma_before_tag');
545 RETURN 0;
546 END IF;
547 END LOOP;
548 IF (NOT bis_vg_util.equal_pointers
549 ( l_select_pointer
550 , p_start_pointer
551 , x_return_status
552 , x_error_Tbl
553 )
554 )
555 THEN
556 x_select_pointer := bis_vg_util.decrement_pointer
557 ( p_View_Select_Text_Table
558 , l_select_pointer
559 , x_return_status
560 , x_error_Tbl
561 );
562 END IF;
563 bis_debug_pub.Add('< exclude_comma_before_tag');
564 RETURN 1;
565 EXCEPTION
566 when FND_API.G_EXC_ERROR then
567 x_return_status := FND_API.G_RET_STS_ERROR ;
568 RAISE FND_API.G_EXC_ERROR;
569 when FND_API.G_EXC_UNEXPECTED_ERROR then
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
572 when others then
573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
574 BIS_VG_UTIL.Add_Error_Message
575 ( p_error_msg_id => SQLCODE
576 , p_error_description => SQLERRM
577 , p_error_proc_name => G_PKG_NAME||'. exclude_comma_before_tag'
578 , p_error_table => x_error_tbl
579 , x_error_table => x_error_tbl
580 );
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END exclude_comma_before_tag;
583 --
584 -- ============================================================================
585 --PROCEDURE : exclude_comma_after_tag
586 --PARAMETERS:
587 -- 1. p_View_Select_Text_Table contains select text
588 -- 2. p_Select_Pointer in pointer
589 -- 3. p_end_pointer pointer to beginning of select table
590 -- 4. x_Select_Pointer out pointer
591 -- 5. x_return_status error or normal
592 -- 6. x_error_Tbl table of error messages
593 --COMMENT : Call this procedure to position the pointer to handle the comma
594 -- when in remove_tags_mode or when _DF with no segments encountered
595 --Return : 1 iff found a comma else 0
596 --EXCEPTION : None
597 -- ============================================================================
598 PROCEDURE exclude_comma_after_tag -- PRIVATE PROCEDURE
599 ( p_View_Select_Text_Table IN bis_vg_types.View_Text_Table_Type
600 , p_Select_Pointer IN bis_vg_types.view_character_pointer_type
601 , p_end_pointer IN bis_vg_types.view_character_pointer_type
602 , x_Select_Pointer OUT bis_vg_types.view_character_pointer_type
603 , x_return_status OUT VARCHAR2
604 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
605 )
606 IS
607 --
608 l_select_pointer bis_vg_types.view_character_pointer_type;
609 --
610 BEGIN
611 bis_debug_pub.Add('> exclude_comma_after_tag');
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613 l_select_pointer := p_Select_Pointer;
614 WHILE( bis_vg_util.get_char( p_View_Select_Text_Table
615 , l_select_pointer
616 , x_return_status
617 , x_error_Tbl
618 )
619 <> ','
620 ) LOOP
621 l_select_pointer := bis_vg_util.increment_pointer
622 ( p_View_Select_Text_Table
623 , l_select_pointer
624 , x_return_status
625 , x_error_Tbl
626 );
627 IF(bis_vg_util.equal_pointers
628 ( l_select_pointer
629 , p_end_pointer
630 , x_return_status
631 , x_error_Tbl
632 )
633 )
634 THEN
635 x_select_pointer := p_select_pointer;
636 bis_debug_pub.Add('< exclude_comma_after_tag');
637 RETURN;
638 END IF;
639 END LOOP;
640 -- we are currently pointing to ',' position beyond that
641 IF (NOT bis_vg_util.equal_pointers
642 ( l_select_pointer
643 , p_end_pointer
644 , x_return_status
645 , x_error_Tbl
646 )
647 )
648 THEN
649 x_select_pointer := bis_vg_util.increment_pointer
650 ( p_View_Select_Text_Table
651 , l_select_pointer
652 , x_return_status
653 , x_error_Tbl
654 );
655 END IF;
656 bis_debug_pub.Add('< exclude_comma_after_tag');
657
658 EXCEPTION
659 when FND_API.G_EXC_ERROR then
660 x_return_status := FND_API.G_RET_STS_ERROR ;
661 RAISE FND_API.G_EXC_ERROR;
662 when FND_API.G_EXC_UNEXPECTED_ERROR then
663 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665 when others then
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
667 BIS_VG_UTIL.Add_Error_Message
668 ( p_error_msg_id => SQLCODE
669 , p_error_description => SQLERRM
670 , p_error_proc_name => G_PKG_NAME||'. exclude_comma_after_tag'
671 , p_error_table => x_error_tbl
672 , x_error_table => x_error_tbl
673 );
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END exclude_comma_after_tag;
676
677
678 --- ========================================================================
679 --- PROCEDURE : get_base_table_cols
680 --- PARAMETERS:
681 --- 1. p_extra_columns IN superset_table_type
682 --- contains all columns to be added to the view from
683 --- the base table, as selected in APPS integrator
684 --- 2. x_unique_tables OUT superset_summary_table_type
685 --- table built in this procedure, contains all unique
686 --- table names referenced in p_extra_columns.
687 --- 3. x_view_column_text_table OUT bis_vg_types.View_Text_Table_Type
688 --- table of all old + new column names
689 --- 4. x_error_Tbl table of error messages
690 --- COMMENT : Call this procedure to add extra columns selected from
691 --- the view base tables in APPS Integrator to the column list
692 --- of the view, and to prepare a summary table for use in
693 --- get_base_table_selects.
694 --- =========================================================================
695 PROCEDURE Get_base_Table_Cols
696 ( p_extra_columns IN superset_table_type
697 , x_unique_tables OUT superset_summary_table_type
698 , x_column_table OUT bis_vg_types.View_Text_Table_Type
699 , x_error_Tbl OUT BIS_VG_UTIL.error_tbl_type
700 )
701 IS
702 l_table_count PLS_INTEGER ;
703
704 BEGIN
705 --
706 l_table_count := 0;
707 bis_debug_pub.Add('> Get_base_Table_Cols');
708 x_unique_tables := superset_summary_table_type();
709
710 FOR l_col_count IN p_extra_columns.FIRST..p_extra_columns.LAST
711 LOOP
712 x_column_table(l_col_count) :=
713 p_extra_columns(l_col_count).table_alias
714 || '_'
715 || p_extra_columns(l_col_count).column_name ;
716 bis_debug_pub.ADD(' x_column_table('||l_col_count
717 ||') = '
718 || x_column_table(l_col_count));
719 IF ( l_col_count = 1 --- first time
720 OR
721 x_unique_tables(l_table_count).table_name --- table_name changed
722 <> p_extra_columns(l_col_count).table_name
723 OR
724 x_unique_tables(l_table_count).table_alias ---table_alias changed
725 <>
726 p_extra_columns(l_col_count).table_alias
727 )
728 THEN
729 --- add new summary entry
730
731 x_unique_tables.extend;
732 l_table_count := l_table_count +1;
733 bis_debug_pub.ADD('Adding entry number '||l_table_count
734 || ' to x_unique_tables'); x_unique_tables(l_table_count).table_name :=
735 p_extra_columns(l_col_count).table_name;
736 bis_debug_pub.ADD(' x_unique_tables('||l_table_count
737 ||').table_name = '
738 || x_unique_tables(l_table_count).table_name);
739 x_unique_tables(l_table_count).table_alias :=
740 p_extra_columns(l_col_count).table_alias;
741 x_unique_tables(l_table_count).currently_valid := FALSE;
742 x_unique_tables(l_table_count).first_record := l_col_count;
743 x_unique_tables(l_table_count).last_record := l_col_count;
744 ELSE
745 x_unique_tables(l_table_count).last_record := l_col_count;
746 END IF;
747
748 END LOOP;
749
750 bis_debug_pub.Add('< Get_base_Table_Cols');
751
752 EXCEPTION
753 when FND_API.G_EXC_ERROR then
754 RAISE FND_API.G_EXC_ERROR;
755 when FND_API.G_EXC_UNEXPECTED_ERROR then
756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
757 when others then
758 BIS_VG_UTIL.Add_Error_Message
759 ( p_error_msg_id => SQLCODE
760 , p_error_description => SQLERRM
761 , p_error_proc_name => G_PKG_NAME||'.Get_base_Table_Cols'
762 , p_error_table => x_error_tbl
763 , x_error_table => x_error_tbl
764 );
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END Get_base_Table_Cols;
767
768
769 --- ========================================================================
770 --- PROCEDURE : get_base_table_selects
771 --- PARAMETERS:
772 --- 1. p_unique_tables IN superset_summary_table_type
773 --- contains previously parsed column names
774 --- 2. p_extra_columns IN superset_table_type
775 --- table built in get_base_table_selects when the first
776 --- union branch was parsed. Contains all unique table
777 --- names referenced in p_extra_columns.
778 --- 3. p_select_table IN bis_vg_types.View_Text_Table_Type
779 --- contains the "FROM" clause of the union branch
780 --- currently being parsed
781 --- 4. x_select_table OUT bis_vg_types.View_Text_Table_Type
782 --- contains all the selects for the extra columns, with
783 --- a alias.column reference if the table name and alias
784 --- exist in this union branch, or a "NULL" if it does not.
785 --- 5. x_error_Tbl table of error messages
786 --- COMMENT : Call this procedure to build the select clause for the extra
787 --- columns from the view base tables. This list of select
788 --- references is concatenated to the select clause of the
789 --- generated view, one union barnch at a time.
790 --- =========================================================================
791 PROCEDURE Get_Base_Table_Selects
792 (p_view_select_table IN bis_vg_types.View_Text_Table_Type
793 , p_start_pos IN bis_vg_types.View_Character_Pointer_Type
794 , p_unique_tables IN superset_summary_table_type
795 , p_extra_columns IN superset_table_type
796 , x_select_table OUT bis_vg_types.View_Text_Table_Type
797 , x_error_Tbl OUT BIS_VG_UTIL.error_tbl_type
798 )
799 IS
800 l_pos bis_vg_types.View_Character_Pointer_Type;
801 l_end PLS_INTEGER;
802 l_dummy VARCHAR2(2000);
803 l_str VARCHAR2(60);
804 l_str2 VARCHAR2(60);
805 l_row VARCHAR2(2000);
806 l_unique_tabs SUPERSET_SUMMARY_TABLE_TYPE;
807 BEGIN
808 --
809 bis_debug_pub.Add('> Get_Base_Table_Selects');
810 l_unique_tabs := p_unique_tables;
811
812 l_str := bis_vg_parser.get_string_token --- get the 'FROM'
813 ( p_view_select_table(p_start_pos.row_num)
814 , p_start_pos.col_num
815 , ', '||g_newline||g_tab
816 , l_end
817 , l_dummy
818 , x_error_Tbl
819 );
820 IF l_end IS NULL THEN ---- reached end of row
821 l_pos := bis_vg_util.increment_pointer_by_row
822 ( p_view_select_table
823 , p_start_pos
824 , l_dummy
825 , x_error_Tbl
826 );
827 ELSE
828 l_pos := p_start_pos;
829 l_pos.col_num := l_end;
830 END IF;
831
832
833 WHILE (bis_vg_util.null_pointer(l_pos, l_dummy, x_error_tbl) = FALSE)
834
835 LOOP
836 l_str := upper(bis_vg_parser.get_string_token
837 ( p_view_select_table(l_pos.row_num)
838 ,l_pos.col_num
839 , ', '||g_newline||g_tab
840 , l_end
841 , l_dummy
842 , x_error_Tbl
843 )
844 );
845 EXIT when (l_str = 'WHERE'
846 OR
847 l_str = 'UNION'
848 );
849 IF l_end IS NULL
850 THEN
851 l_pos := bis_vg_util.increment_pointer_by_row
852 ( p_view_select_table
853 , l_pos
854 , l_dummy
855 , x_error_Tbl
856 );
857
858 ELSE l_pos.col_num := l_end;
859 END IF;
860 ---
861 FOR l_table_count IN p_unique_tables.FIRST..p_unique_tables.last
862 LOOP --- over the list of table names
863 IF l_str = p_unique_tables(l_table_count).table_name
864 THEN --- found table, look for alias
865 IF (
866 p_unique_tables(l_table_count).table_alias
867 =
868 upper(bis_vg_parser.get_string_token
869 ( p_view_select_table(l_pos.row_num)
870 ,l_pos.col_num
871 , ', '||g_newline||g_tab
872 , l_end
873 , l_dummy
874 , x_error_Tbl
875 )
876 )
877 )
878 THEN
879 l_unique_tabs(l_table_count).currently_valid := TRUE;
880 --- increment l_pos to the next string
881 IF l_end IS NULL
882 THEN
883 l_pos := bis_vg_util.increment_pointer_by_row
884 ( p_view_select_table
885 , l_pos
886 , l_dummy
887 , x_error_Tbl
888 );
889 ELSE
890 l_pos.col_num := l_end;
891 END IF; --- l_end is null
892 END IF; --- alias found
893 END IF; --- table_name found
894 END LOOP; --- over the list of table names
895
896 END LOOP; --- over the select statement string table
897
898
899 FOR table_count IN p_unique_tables.FIRST..p_unique_tables.last
900 LOOP
901 IF (l_unique_tabs(table_count).currently_valid = FALSE)
902 THEN
903 FOR
904 column_count
905 IN
906 p_unique_tables(table_count).first_record
907 ..
908 p_unique_tables(table_count).last_record
909 LOOP
910 x_select_table(column_count) := ', NULL';
911 END LOOP; --- column loop
912
913 ELSE
914 FOR
915 column_count
916 IN
917 p_unique_tables(table_count).first_record
918 ..
919 p_unique_tables(table_count).last_record
920 LOOP
921 x_select_table(column_count) :=
922 ' , '
923 || p_extra_columns(column_count).table_alias
924 || '.'
925 || p_extra_columns(column_count).column_name;
926 END LOOP; --- column loop
927
928 END IF; --- valid or invalid
929 END LOOP; --- table loop
930
931 bis_debug_pub.Add('< Get_Base_Table_Selects');
932
933
934 EXCEPTION
935 when FND_API.G_EXC_ERROR then
936 RAISE FND_API.G_EXC_ERROR;
937 when FND_API.G_EXC_UNEXPECTED_ERROR then
938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
939 when others then
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||'.Get_Base_Table_Selects'
944 , p_error_table => x_error_tbl
945 , x_error_table => x_error_tbl
946 );
947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; END get_base_table_selects;
948
949 --- ========================================================================
950 --- PROCEDURE : Update_View
951 --- PARAMETERS:
952 --- 1. p_View_Column_Text_Table IN bis_vg_types.View_Text_Table_Type:
953 --- contains column names
954 --- 2. p_View_Select_Text_Table IN bis_vg_types.View_Text_Table_Type:
955 --- contains select text
956 --- 3. p_mode IN bi_vg_types.View_Generator_Mode_type:
957 --- production, plsql or test
958 --- 4. p_selected_columns IN BIS_VG_TYPES.flexfield_column_table_type
959 --- Used only for calls from generate_pruned_view
960 --- 5. p_extra_columns IN superset_table_type
961 --- Used only for calls from generate_pruned_view
962 --- 6. x_View_Column_Text_Table OUT bis_vg_types.View_Text_Table_Type:
963 --- return column names
964 --- 7. x_View_Select_Text_Table OUT bis_vg_types.View_Text_Table_Type
965 --- return select text
966 --- 8. x_View_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
967 --- returns table of records with comments
968 --- for flex derived columns.
969 --- 8. x_error_Tbl table of error messages
970 --- COMMENT : Call this procedure to update the flex filed in a view
971 --- ==========================================================================
972
973 PROCEDURE Update_View --- PRIVATE PROCEDURE
974 ( p_View_Column_Text_Table IN bis_vg_types.View_Text_Table_Type
975 , p_View_Select_Text_Table IN bis_vg_types.view_text_table_type
976 , p_mode IN NUMBER := bis_vg_types.sqlplus_production_mode
977 , p_selected_columns IN BIS_VG_TYPES.flexfield_column_table_type := NULL
978 , p_extra_columns IN superset_table_type := NULL
979 , x_View_Column_Text_Table OUT bis_vg_types.View_Text_Table_Type
980 , x_View_Select_Text_Table OUT bis_vg_types.View_Text_Table_Type
981 , x_View_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
982 , x_error_Tbl OUT BIS_VG_UTIL.error_tbl_type
983 )
984 IS
985 l_col_tab_curr_pos bis_vg_types.view_character_pointer_type;
986 l_sel_tab_curr_pos bis_vg_types.view_character_pointer_type;
987 l_col_tab_past_pos bis_vg_types.view_character_pointer_type;
988 l_sel_tab_past_pos bis_vg_types.view_character_pointer_type;
989 l_sel_tab_pretag_pos bis_vg_types.view_character_pointer_type;
990 l_col_tab_pretag_pos bis_vg_types.view_character_pointer_type;
991 l_sel_tab_FROM_pos bis_vg_types.view_character_pointer_type;
992 l_sel_tab_SELECT_pos bis_vg_types.view_character_pointer_type;
993 l_sel_tab_sec_tag_pos bis_vg_types.view_character_pointer_type;
994 l_select_string_table bis_vg_types.view_text_table_type;
995 l_union_string_table bis_vg_types.view_text_table_type;
996 l_flex_string_table bis_vg_types.view_text_table_type;
997 l_from_string_table bis_vg_types.view_text_table_type;
998 l_security_string_table bis_vg_types.view_text_table_type;
999 l_column_table bis_vg_types.view_text_table_type;
1000 l_select_table bis_vg_types.view_text_table_type;
1001 l_column_comment_table bis_vg_types.Flex_Column_Comment_Table_Type;
1002 l_temp_column_table bis_vg_types.view_text_table_type;
1003 l_temp_select_table bis_vg_types.view_text_table_type;
1004 l_unique_tables superset_summary_table_type;
1005 l_done BOOLEAN;
1006 l_select_count NUMBER;
1007 l_in_union BOOLEAN ;
1008 l_dummy VARCHAR2(2000);
1009 l_hcr NUMBER;
1010 --
1011 BEGIN
1012 --
1013 l_in_union := FALSE;
1014 bis_debug_pub.Add('> Update_View');
1015 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
1016
1017
1018
1019
1020 --- --- --- DEBUG ---
1021
1022 --- IF (g_mode = bis_vg_types.test_view_gen_mode) THEN
1023 --- bis_debug_pub.debug_on;
1024 --- END IF;
1025 ---
1026 --- bis_vg_util.print_View_Text
1027 --- ( p_View_Column_Text_Table
1028 --- , l_dummy
1029 --- , x_error_Tbl
1030 --- );
1031 --- bis_vg_util.print_View_Text
1032 --- ( p_View_Select_Text_Table
1033 --- , l_dummy
1034 --- , x_error_Tbl
1035 --- );
1036 ---
1037 --- IF (g_mode = bis_vg_types.test_view_gen_mode) THEN
1038 --- bis_debug_pub.debug_off;
1039 --- END IF;
1040 ---
1041 l_flex_string_table(1) := '_KF';
1042 l_flex_string_table(2) := '_DF';
1043 l_flex_string_table(3) := '_LA';
1044 --
1045 l_from_string_table(1) := 'FROM';
1046 l_select_string_table(1) := 'SELECT';
1047 l_union_string_table(1) := 'UNION';
1048 l_security_string_table(1) := '_SEC:';
1049 --
1050 l_sel_tab_curr_pos.row_num := 1;
1051 l_sel_tab_curr_pos.col_num := 1;
1052 l_sel_tab_SELECT_pos := l_sel_tab_curr_pos;
1053 --
1054 WHILE (l_sel_tab_curr_pos.row_num IS NOT null) LOOP
1055 -- find the next from pointer
1056 bis_debug_pub.Add('before seeking from');
1057 l_sel_tab_FROM_pos := bis_vg_parser.get_keyword_position
1058 ( p_view_select_text_table
1059 , l_from_string_table
1060 , l_sel_tab_curr_pos
1061 , l_dummy
1062 , x_error_Tbl
1063 );
1064 bis_debug_pub.Add('after seeking from');
1065 -- reset column pointer
1066 l_col_tab_curr_pos.row_num := 1;
1067 l_col_tab_curr_pos.col_num := 1;
1068 --
1069 l_col_tab_past_pos := l_col_tab_curr_pos;
1070 l_sel_tab_past_pos := l_sel_tab_curr_pos;
1071 --
1072 -- iterate with the columns
1073 WHILE (l_col_tab_curr_pos.row_num IS NOT NULL) LOOP
1074 bis_debug_pub.Add('column row = '||l_col_tab_curr_pos.row_num||
1075 ' column col = '||l_col_tab_curr_pos.col_num);
1076 -- find the new keyword
1077 l_col_tab_curr_pos := get_tag_keyword_position
1078 ( p_view_column_text_table
1079 , l_flex_string_table
1080 , l_col_tab_past_pos
1081 , l_dummy
1082 , x_error_Tbl
1083 );
1084 --- --- --- DEBUG ---
1085 --- bis_vg_util.print_view_pointer( l_col_tab_past_pos
1086 --- , l_dummy
1087 --- , x_error_Tbl
1088 --- );
1089 IF (l_col_tab_curr_pos.row_num IS NOT NULL) THEN
1090 -- found a valid flex field
1091 -- find the new keyword
1092 l_sel_tab_curr_pos := get_tag_keyword_position
1093 ( p_view_select_text_table
1094 , l_flex_string_table
1095 , l_sel_tab_past_pos
1096 , l_dummy
1097 , x_error_Tbl
1098 );
1099 l_col_tab_pretag_pos := l_col_tab_curr_pos;
1100 l_sel_tab_pretag_pos := l_sel_tab_curr_pos;
1101 bis_debug_pub.Add('PREV COLUMN POINTER');
1102 --- --- --- DEBUG ---
1103 --- bis_vg_util.print_view_pointer( l_col_tab_past_pos
1104 --- , l_dummy
1105 --- , x_error_Tbl
1106 --- );
1107 -- update the flex fields
1108 -- and copy to output tables
1109 update_flexfields( p_View_Column_Text_Table
1110 , p_view_select_text_table
1111 , p_mode
1112 , p_selected_columns
1113 , l_col_tab_curr_pos
1114 , l_sel_tab_curr_pos
1115 , l_Column_Table
1116 , l_Select_Table
1117 , l_Column_Comment_Table
1118 , l_col_tab_curr_pos
1119 , l_sel_tab_curr_pos
1120 , l_dummy
1121 , x_error_Tbl
1122 );
1123 -- position the pointer before the last single quote in select
1124 l_sel_tab_pretag_pos := bis_vg_util.position_before_characters
1125 ( p_view_select_text_table
1126 , '''' ----||' '||' '
1127 , l_sel_tab_pretag_pos
1128 , l_dummy
1129 , x_error_Tbl
1130 );
1131 --- --- --- DEBUG ---
1132 --- bis_vg_util.print_view_pointer ( l_sel_tab_pretag_pos
1133 --- , l_dummy
1134 --- , x_error_Tbl
1135 --- );
1136 -- if tag generated an empty table (empty _DF or remove_tags mode)
1137 -- and its not the first column in a UNION clause
1138 l_hcr := 1;
1139 IF (l_select_table.COUNT = 0) THEN
1140 -- no flex definition or remove_tags mode,
1141 -- remove the comma prior to the tag just processed in the
1142 -- select table. (Note: no commas in column table yet)
1143 l_hcr := exclude_comma_before_tag( p_view_select_text_table
1144 , l_sel_tab_pretag_pos
1145 , l_sel_tab_SELECT_pos
1146 , l_sel_tab_pretag_pos
1147 , l_dummy
1148 , x_error_Tbl
1149 );
1150 IF (l_hcr = 0) THEN
1151 -- No valid columns prior to current tag in select statement.
1152 -- Current (unexpanded) tag is first tag, so we must remove the
1153 -- trailing ',' in order for the next column to be first in
1154 -- the generated select statement
1155 exclude_comma_after_tag( p_view_select_text_table
1156 , l_sel_tab_curr_pos
1157 , l_sel_tab_FROM_pos
1158 , l_sel_tab_curr_pos
1159 , l_dummy
1160 , x_error_Tbl
1161 );
1162
1163 END IF;
1164
1165 END IF; -- end of tag generated an empty table
1166 --
1167 bis_debug_pub.ADD('right after positioning before');
1168 -- bis_vg_util.print_view_pointer
1169 -- ( l_sel_tab_pretag_pos
1170 -- , l_dummy
1171 -- , x_error_Tbl
1172 -- );
1173 --
1174 -- pointers have been decremented to point just before ' or '
1175 -- if we have a valid character at the current position, we need
1176 -- to increment the pointers as the copy function copies
1177 -- exclusive of the end pointer
1178 --
1179 l_sel_tab_pretag_pos := BIS_VG_UTIL.increment_pointer
1180 ( p_view_select_text_table
1181 , l_sel_tab_pretag_pos
1182 , l_dummy
1183 , x_error_Tbl
1184 );
1185 -- copy the portion between the prev and current pointer
1186 -- to output tables
1187 --
1188 bis_debug_pub.Add('PREV COLUMN POINTER');
1189 --- --- --- DEBUG ---
1190 --- bis_vg_util.print_view_pointer
1191 --- (l_col_tab_past_pos
1192 --- , l_dummy
1193 --- , x_error_Tbl
1194 --- );
1195 bis_vg_util.copy_part_of_table
1196 ( p_view_column_text_table
1197 , l_col_tab_past_pos
1198 , l_col_tab_pretag_pos
1199 , l_temp_column_table
1200 , l_dummy
1201 , x_error_Tbl
1202 );
1203 bis_debug_pub.ADD('Copy column table is ');
1204 bis_vg_util.print_view_text(l_temp_column_table
1205 , l_dummy
1206 , x_error_Tbl
1207 );
1208
1209 IF(l_in_union = FALSE) THEN
1210 --- Column table only traversed once for a union
1211 bis_vg_util.concatenate_tables( x_view_column_text_table
1212 , l_temp_column_table
1213 , x_view_column_text_table
1214 , l_dummy
1215 , x_error_Tbl
1216 );
1217
1218 --- --- --- DEBUG ---
1219 --- bis_debug_pub.ADD('Concatenated column table is ');
1220 --- bis_vg_util.print_view_text
1221 --- ( x_view_column_text_table
1222 --- , l_dummy
1223 --- , x_error_Tbl
1224 --- );
1225 END IF;
1226 --
1227 --- --- --- DEBUG ---
1228 --- bis_debug_pub.ADD('beore Copy select table');
1229 --- bis_vg_util.print_view_pointer
1230 --- ( l_sel_tab_past_pos
1231 --- , l_dummy
1232 --- , x_error_Tbl
1233 --- );
1234 --- bis_debug_pub.ADD( 'l_char := '||
1235 --- bis_vg_util.get_char( p_view_select_text_table
1236 --- , l_sel_tab_past_pos
1237 --- , l_dummy
1238 --- , x_error_Tbl
1239 --- )
1240 --- );
1241 ---
1242 bis_vg_util.copy_part_of_table( p_view_select_text_table
1243 , l_sel_tab_past_pos
1244 , l_sel_tab_pretag_pos
1245 , l_temp_select_table
1246 , l_dummy
1247 , x_error_Tbl
1248 );
1249 --- --- --- DEBUG ---
1250 --- bis_debug_pub.ADD('Copy select table is ');
1251 --- bis_vg_util.print_view_text
1252 --- ( l_temp_select_table
1253 --- , l_dummy
1254 --- , x_error_Tbl
1255 --- );
1256 ---
1257 bis_vg_util.concatenate_tables( x_view_select_text_table
1258 , l_temp_select_table
1259 , x_view_select_text_table
1260 , l_dummy
1261 , x_error_Tbl
1262 );
1263
1264 --- --- --- DEBUG ---
1265 --- bis_debug_pub.ADD('Concatenated select table is ');
1266 --- bis_vg_util.print_view_text
1267 --- ( x_view_select_text_table
1268 --- , l_dummy
1269 --- , x_error_Tbl
1270 --- );
1271 ---
1272
1273
1274 IF (l_column_table.COUNT > 0) THEN
1275 --- concatenate expanded flexfield columns to output
1276 IF(l_in_union = FALSE) THEN
1277 --- first the column headings
1278 bis_vg_util.concatenate_tables( x_view_column_text_table
1279 , l_column_table
1280 , x_view_column_text_table
1281 , l_dummy
1282 , x_error_Tbl
1283 );
1284 --- get the column comments
1285 bis_vg_util.concatenate_tables( x_view_column_comment_table
1286 , l_column_comment_table
1287 , x_view_column_comment_table
1288 , l_dummy
1289 , x_error_Tbl
1290 );
1291 END IF;
1292 --- second - the select statement
1293 bis_vg_util.concatenate_tables( x_view_select_text_table
1294 , l_select_table
1295 , x_view_select_text_table
1296 , l_dummy
1297 , x_error_Tbl
1298 );
1299 END IF;
1300
1301 bis_debug_pub.Add('after concatenation of tables');
1302
1303 --- --- --- DEBUG ---
1304 --- bis_vg_util.print_view_text
1305 --- ( x_view_column_text_table
1306 --- , l_dummy
1307 --- , x_error_Tbl
1308 --- );
1309 --- bis_vg_util.print_view_text
1310 --- (x_view_select_text_table
1311 --- , l_dummy
1312 --- , x_error_Tbl
1313 --- );
1314 ---
1315 -- save the pointers as previous
1316 l_col_tab_past_pos := l_col_tab_curr_pos;
1317 l_sel_tab_past_pos := l_sel_tab_curr_pos;
1318 END IF; -- end column not null
1319 --
1320 END LOOP; -- end column pointer loop
1321
1322 --- --- --- DEBUG ---
1323 --
1324 --- bis_debug_pub.Add('out of col loop');
1325 --
1326 -- out of the columns, copy the last part of the column table
1327 IF(l_in_union = FALSE) THEN
1328 bis_vg_util.copy_part_of_table
1329 ( p_view_column_text_table
1330 , l_col_tab_past_pos
1331 , l_col_tab_curr_pos --- defaults to end of table if null
1332 , l_column_table
1333 , l_dummy
1334 , x_error_Tbl
1335 );
1336
1337 --- --- --- DEBUG ---
1338 --- bis_debug_pub.ADD('Copy column table is ');
1339 --- bis_vg_util.print_view_text(l_column_table
1340 --- , l_dummy
1341 --- , x_error_Tbl
1342 --- );
1343
1344 bis_vg_util.concatenate_tables( x_view_column_text_table
1345 , l_column_table
1346 , x_view_column_text_table
1347 , l_dummy
1348 , x_error_Tbl
1349 );
1350
1351 --- --- --- DEBUG ---
1352 --- bis_debug_pub.ADD('Concatenated column table is ');
1353 --- bis_vg_util.print_view_text( x_view_column_text_table
1354 --- , l_dummy
1355 --- , x_error_Tbl
1356 --- );
1357
1358 END IF;
1359 -- prepare to copy the select table
1360 -- find the security pointer
1361
1362
1363 IF (p_extra_columns IS NOT NULL
1364 AND p_extra_columns.COUNT > 0
1365 AND x_view_column_text_table.COUNT > 0
1366 )
1367 THEN
1368 --- --- --- DEBUG ---
1369 --- bis_vg_util.print_view_pointer ( l_sel_tab_FROM_pos
1370 --- , l_dummy
1371 --- , x_error_Tbl
1372 --- );
1373 IF (l_in_union = FALSE)
1374 THEN
1375 get_base_table_cols( p_extra_columns
1376 , l_unique_tables
1377 , l_Column_Table
1378 , x_error_Tbl
1379 );
1380 bis_vg_util.concatenate_tables( x_view_column_text_table
1381 , l_column_table
1382 , x_view_column_text_table
1383 , l_dummy
1384 , x_error_Tbl
1385 );
1386
1387
1388 END IF;
1389
1390 --- bis_debug_pub.Add('before seeking where');
1391
1392 --- bis_debug_pub.Add('after seeking where');
1393 get_base_table_selects(p_view_select_text_table
1394 , l_sel_tab_from_pos
1395 , l_unique_tables
1396 , p_extra_columns
1397 , l_select_table
1398 , x_error_Tbl
1399 );
1400
1401 bis_vg_util.concatenate_tables( x_view_select_text_table
1402 , l_select_table
1403 , x_view_select_text_table
1404 , l_dummy
1405 , x_error_Tbl
1406 );
1407
1408 END IF;
1409
1410 IF (p_mode = bis_vg_types.remove_tags_mode) THEN
1411 l_sel_tab_sec_tag_pos := NULL;
1412 ELSE
1413
1414 l_sel_tab_sec_tag_pos := bis_vg_parser.get_keyword_position
1415 ( p_view_select_text_table
1416 , l_security_string_table
1417 , l_sel_tab_FROM_pos
1418 , l_dummy
1419 , x_error_Tbl
1420 );
1421 --- --- --- DEBUG ---
1422 ---
1423
1424 --- bis_vg_util.print_view_pointer ( l_sel_tab_sec_tag_pos
1425 --- , l_dummy
1426 --- , x_error_Tbl
1427 --- );
1428 ---
1429 -- see if where pointer pointing to security is good
1430 IF (bis_vg_util.null_pointer ( l_sel_tab_sec_tag_pos
1431 , l_dummy
1432 , x_error_Tbl
1433 )
1434 = FALSE
1435 )
1436 THEN
1437 -- copy part of select table from FROM pointer to security pointer
1438 bis_debug_pub.Add('security pointer is not null');
1439 l_sel_tab_pretag_pos := bis_vg_util.position_before_characters
1440 ( p_view_select_text_table
1441 , ' ,'
1442 , l_sel_tab_sec_tag_pos
1443 , l_dummy
1444 , x_error_Tbl
1445 );
1446 bis_vg_util.copy_part_of_table( p_view_select_text_table
1447 , l_sel_tab_past_pos
1448 , l_sel_tab_pretag_pos
1449 , l_select_table
1450 , l_dummy
1451 , x_error_Tbl
1452 );
1453 bis_vg_util.concatenate_tables( x_view_select_text_table
1454 , l_select_table
1455 , x_view_select_text_table
1456 , l_dummy
1457 , x_error_Tbl
1458 );
1459 bis_vg_security.add_security_Info( p_View_Select_Text_Table
1460 , l_sel_tab_sec_tag_pos
1461 , l_select_table
1462 , l_sel_tab_past_pos
1463 , l_dummy
1464 , x_error_Tbl
1465 );
1466
1467 --- --- --- DEBUG ---
1468 ---
1469 --- bis_debug_pub.Add('security pointer after add security info');
1470 --- bis_vg_util.print_view_pointer ( l_sel_tab_past_pos
1471 --- , l_dummy
1472 --- , x_error_Tbl
1473 --- );
1474 ---
1475 bis_vg_util.concatenate_tables( x_view_select_text_table
1476 , l_select_table
1477 , x_view_select_text_table
1478 , l_dummy
1479 , x_error_Tbl
1480 );
1481 ELSE
1482 bis_debug_pub.Add('security pointer is null');
1483 END IF; --- SEC tag is valid
1484 END IF; --- remove tags mode
1485
1486
1487 --
1488 IF (l_sel_tab_past_pos.row_num IS NOT NULL) THEN
1489 -- position at the next select statement
1490 --
1491
1492 --- --- --- DEBUG ---
1493 --- bis_vg_util.print_view_pointer ( l_sel_tab_past_pos
1494 --- , l_dummy
1495 --- , x_error_Tbl
1496 --- );
1497
1498 --- Look for keyword UNION
1499 l_sel_tab_curr_pos := bis_vg_parser.get_keyword_position
1500 ( p_view_select_text_table
1501 , l_union_string_table
1502 , l_sel_tab_past_pos
1503 , l_dummy
1504 , x_error_Tbl
1505 );
1506
1507
1508 --- --- --- DEBUG ---
1509 ---
1510 --- bis_vg_util.print_view_pointer ( l_sel_tab_curr_pos
1511 --- , l_dummy
1512 --- , x_error_Tbl
1513 --- );
1514 ---
1515 IF (l_sel_tab_curr_pos.row_num IS NOT NULL) THEN
1516 bis_debug_pub.ADD('more select info');
1517 l_sel_tab_curr_pos := bis_vg_parser.get_keyword_position
1518 ( p_view_select_text_table
1519 , l_select_string_table
1520 , l_sel_tab_curr_pos
1521 , l_dummy
1522 , x_error_Tbl
1523 );
1524 END IF;
1525 --
1526 l_sel_tab_SELECT_pos := l_sel_tab_curr_pos;
1527 -- copy up to next select statement
1528 bis_vg_util.copy_part_of_table( p_view_select_text_table
1529 , l_sel_tab_past_pos
1530 , l_sel_tab_curr_pos
1531 , l_select_table
1532 , l_dummy
1533 , x_error_Tbl
1534 );
1535
1536
1537 --- --- --- DEBUG ---
1538 ---
1539 --- bis_debug_pub.ADD('Copy select table is ');
1540 --- bis_vg_util.print_view_text(l_select_table
1541 --- , l_dummy
1542 --- , x_error_Tbl
1543 --- );
1544 ---
1545 bis_vg_util.concatenate_tables( x_view_select_text_table
1546 , l_select_table
1547 , x_view_select_text_table
1548 , l_dummy
1549 , x_error_Tbl
1550 );
1551 /*
1552 bis_vg_util.concatenate_tables( x_view_column_comment_table
1553 , l_column_comment_table
1554 , x_view_column_comment_table
1555 , l_dummy
1556 , x_error_Tbl
1557 );
1558 */
1559
1560 --- --- --- DEBUG ---
1561 ---
1562 --- bis_debug_pub.ADD('Concatenated select table is ');
1563 --- bis_vg_util.print_view_text ( x_view_select_text_table
1564 --- , l_dummy
1565 --- , x_error_Tbl
1566 --- );
1567 ---
1568 l_in_union := TRUE;
1569
1570 END IF;
1571
1572 END LOOP;
1573
1574 --- --- --- DEBUG ---
1575 ---
1576 --- bis_debug_pub.ADD('out of the select loop');
1577 --- bis_vg_util.print_View_Text
1578 --- ( x_View_Column_Text_Table
1579 --- , l_dummy
1580 --- , x_error_Tbl
1581 --- p );
1582 --- bis_vg_util.print_View_Text
1583 --- ( x_View_Select_Text_Table
1584 --- , l_dummy
1585 --- , x_error_Tbl
1586 --- );
1587 bis_debug_pub.Add('< Update_View');
1588 --
1589 EXCEPTION
1590 when FND_API.G_EXC_ERROR then
1591 bis_debug_pub.add('x_error_tbl.count = '||x_error_tbl.count);
1592 --- x_return_status := FND_API.G_RET_STS_ERROR ;
1593 RAISE FND_API.G_EXC_ERROR;
1594 when FND_API.G_EXC_UNEXPECTED_ERROR then
1595 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1597 when others then
1598 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1599 BIS_VG_UTIL.Add_Error_Message
1600 ( p_error_msg_id => SQLCODE
1601 , p_error_description => SQLERRM
1602 , p_error_proc_name => G_PKG_NAME||'.Update_View'
1603 , p_error_table => x_error_tbl
1604 , x_error_table => x_error_tbl
1605 );
1606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1607 --
1608 END Update_View;
1609 --
1610 --
1611 -- ============================================================================
1612 --PROCEDURE : Handle_Gen_Exception
1613 --PARAMETERS:
1614 -- 1. p_ViewName Name of the View
1615 -- 2. p_MsgName Exception Message Name
1616 --COMMENT : Call this procedure to update the flex filed in a view
1617 --EXCEPTION : None
1618 -- ============================================================================
1619 --
1620 PROCEDURE handle_gen_exception
1621 (p_ViewName IN VARCHAR2
1622 , x_return_status OUT VARCHAR2
1623 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1624 )
1625 IS
1626 --
1627 l_error_code NUMBER;
1628 l_error_msg VARCHAR2(2000);
1629 l_count NUMBER;
1630 l_trace VARCHAR2(2000);
1631 l_message VARCHAR2(2000);
1632 l_appl_short VARCHAR2(50);
1633 l_end_pos NUMBER;
1634 --
1635 BEGIN
1636 bis_debug_pub.Add('> handle_gen_exception');
1637 x_return_status := FND_API.G_RET_STS_SUCCESS;
1638 --
1639 -- get first message in message queue
1640 l_error_msg := fnd_msg_pub.get(fnd_msg_pub.G_FIRST, FND_API.G_TRUE);
1641 -- get application short name
1642 l_appl_short := SUBSTR( l_error_msg
1643 , 1
1644 , INSTR(l_error_msg, FND_API.G_MISS_CHAR) - 1
1645 );
1646 l_end_pos := INSTR(l_error_msg, FND_API.G_MISS_CHAR, 1, 2)
1647 - LENGTH(l_appl_short) - 2;
1648 -- get message_name
1649 l_error_msg := SUBSTR(l_error_msg, LENGTH(l_appl_short)+2, l_end_pos);
1650 -- get message_code
1651 l_error_code := fnd_message.get_number( l_appl_short
1652 , l_error_msg
1653 );
1654 -- reset message stack
1655 fnd_msg_pub.reset;
1656 l_count := fnd_msg_pub.count_msg;
1657 --
1658 --- retrieve user-friendly message and pass it as a token for
1659 --- BIS_VG_FAIL_VIEW_NAME_PROMPT
1660 --- assumption: as the message stack is first populated with the
1661 --- message explaining the exception and then with the execution trace for
1662 --- every procedure in the call stack, the user-friendly message is the first
1663 --- one
1664 l_error_msg := fnd_msg_pub.get(p_encoded => FND_API.G_FALSE);
1665 fnd_message.set_name( application => bis_vg_types.MESSAGE_APPLICATION
1666 , name => 'BIS_VG_FAIL_VIEW_NAME_PROMPT'
1667 );
1668 fnd_message.set_token('VIEW_NAME', p_viewname);
1669 fnd_message.set_token('ERROR_NUMBER',l_error_code );
1670 fnd_message.set_token('ERROR_MESSAGE', l_error_msg);
1671 l_message := fnd_message.get;
1672 --
1673 -- get execution trace
1674 l_trace := '';
1675 FOR i IN 2 .. l_count LOOP
1676 l_trace := SUBSTR( l_trace ||
1677 ' ' ||
1678 fnd_msg_pub.get(p_encoded => FND_API.G_FALSE)
1679 , 1
1680 , 2000
1681 );
1682 END LOOP;
1683 --
1684 -- empty message stack
1685 fnd_msg_pub.Initialize;
1686 --
1687 -- add entry to failure log to enable reporting
1688 bis_vg_log.update_failure_log( p_ViewName
1689 , l_error_code
1690 , SUBSTR(l_message||' '||l_trace, 1, 2000)
1691 , x_return_status
1692 , x_error_Tbl
1693 );
1694 bis_debug_pub.Add('< handle_gen_exception');
1695 EXCEPTION
1696 when FND_API.G_EXC_ERROR then
1697 x_return_status := FND_API.G_RET_STS_ERROR ;
1698 RAISE FND_API.G_EXC_ERROR;
1699 when FND_API.G_EXC_UNEXPECTED_ERROR then
1700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1702 when others then
1703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1704 BIS_VG_UTIL.Add_Error_Message
1705 ( p_error_msg_id => SQLCODE
1706 , p_error_description => SQLERRM
1707 , p_error_proc_name => G_PKG_NAME||'. handle_gen_exception'
1708 , p_error_table => x_error_tbl
1709 , x_error_table => x_error_tbl
1710 );
1711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1712 END handle_gen_exception;
1713 --
1714 --
1715 -- ============================================================================
1716 --PROCEDURE : comment_View
1717 --PARAMETERS:
1718 -- 1. p_View_Name name of generated view to comment
1719 -- 2. p_column_Comment_Table PL/SQL table
1720 --COMMENT : This procedure is used to comment flexfield derived column
1721 -- with information as to the source of the flexfield
1722 --EXCEPTION : None
1723 -- ============================================================================
1724 PROCEDURE comment_Flex_Columns
1725 (p_View_Name IN VARCHAR2
1726 , p_column_Comment_Table IN bis_vg_types.Flex_Column_Comment_Table_Type
1727 )
1728 IS
1729
1730 l_comment_stmt VARCHAR2(5000);
1731 l_comment_table_stmt VARCHAR2(100);
1732 BEGIN
1733 bis_debug_pub.Add('> comment_Flex_Columns');
1734
1735 FOR j IN 1 .. p_column_Comment_Table.COUNT LOOP
1736 --- Handle each comment statement in own block
1737 BEGIN
1738
1739 /*
1740 l_comment_stmt := 'COMMENT ON COLUMN '||p_view_name||'.'
1741 ||p_column_comment_table(j).column_name||' IS '''
1742 ||p_column_comment_table(j).flex_type||','
1743 ||p_column_comment_table(j).column_comments||'''';
1744
1745 --for bug 2208122
1746 if (instr(p_column_comment_table(j).column_name, '.') <> 0) then
1747 l_comment_stmt := 'COMMENT ON COLUMN '||p_view_name||'."'
1748 ||p_column_comment_table(j).column_name||'" IS '''
1749 ||p_column_comment_table(j).flex_type||','
1750 ||p_column_comment_table(j).column_comments||'''';
1751 end if;
1752 */
1753
1754 --for bug 2369734: add double quotes to comment for all columns
1755 l_comment_stmt := 'COMMENT ON COLUMN '||p_view_name||'."'
1756 ||p_column_comment_table(j).column_name||'" IS '''
1757 ||p_column_comment_table(j).flex_type||','
1758 ||p_column_comment_table(j).column_comments||'''';
1759 -----------------
1760
1761 EXECUTE IMMEDIATE l_comment_stmt;
1762
1763
1764
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 -- The COMMENT command may have failed due to column name not starting with a letter
1768 -- Try wrapping the column name with double quotes
1769 BEGIN
1770 l_comment_stmt := 'COMMENT ON COLUMN '||p_view_name||'."'
1771 ||p_column_comment_table(j).column_name||'" IS '''
1772 ||p_column_comment_table(j).flex_type||','
1773 ||p_column_comment_table(j).column_comments||'''';
1774 EXECUTE IMMEDIATE l_comment_stmt;
1775
1776 EXCEPTION
1777 -- Will ignore failure to comment column
1778 WHEN OTHERS THEN
1779 null;
1780 END;
1781 END;
1782 END LOOP;
1783 -- Bug 6819715
1784 -- add a comment on the view itself to document the optimizer mode used and the RDBMS major verion number.
1785 l_comment_table_stmt := 'COMMENT ON TABLE ' ||p_view_name|| ' IS '' optimizer mode is '||
1786 BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints||' database version is '||
1787 g_db_version||'''';
1788 BEGIN
1789 EXECUTE IMMEDIATE l_comment_table_stmt;
1790 EXCEPTION
1791 -- Will ignore failure to comment table
1792 WHEN OTHERS THEN
1793 null;
1794
1795 END;
1796 bis_debug_pub.Add('< comment_Flex_Columns');
1797 EXCEPTION
1798 WHEN OTHERs THEN
1799 NULL;
1800 END comment_Flex_Columns;
1801 --
1802 -- =====================
1803 -- PUBLIC PROCEDURES
1804 -- =====================
1805 --
1806
1807 PROCEDURE Generate_Pruned_View
1808 ( p_viewname IN BIS_VG_TYPES.view_name_type
1809 , p_objectname IN varchar2
1810 , p_gen_viewname IN varchar2 := NULL
1811 )
1812
1813 IS
1814 l_instance VARCHAR2(200);
1815 l_SubsetColRec BIS_VG_TYPES.Flexfield_column_rec_Type;
1816 l_subset_table BIS_VG_TYPES.flexfield_column_table_type
1817 := BIS_VG_TYPES.flexfield_column_table_type();
1818 l_SupersetColRec superset_Rec_Type;
1819 l_superset_table superset_table_type
1820 := superset_table_type();
1821 l_View_Column_Text_Table bis_vg_types.View_Text_Table_Type;
1822 l_View_Select_Text_Table bis_vg_types.View_Text_Table_Type;
1823 l_View_Column_Out_Table bis_vg_types.View_Text_Table_Type;
1824 l_View_Select_Out_Table bis_vg_types.View_Text_Table_Type;
1825 l_View_Column_Comment_Table bis_vg_types.Flex_Column_Comment_Table_Type;
1826 l_success VARCHAR2(100); --changed the length from 2 to 100 for bug 4093769
1827 l_error_string VARCHAR2(4000);
1828
1829 --- cursor used to process a dynamic multi-row query
1830 l_FlexColRec_cur Ref_Cursor_Type;
1831
1832 --- local variable not used but maintained for backward compatibility.
1833 l_dummy_Tbl BIS_VG_UTIL.Error_Tbl_Type;
1834
1835 BEGIN
1836 --- --- DEBUG
1837 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - '||p_viewname);
1838 --- FIRST CHECK FOR DATA WAREHOUSE LINK
1839
1840 l_success := bis_debug_pub.set_debug_mode('FILE');
1841 bis_debug_pub.initialize;
1842 bis_debug_pub.setdebuglevel(10);
1843 g_mode := bis_vg_types.production_mode;
1844
1845 BEGIN
1846 execute immediate 'ALTER session SET global_names=FALSE';
1847 --- --- DEBUG
1848 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - Checking Warehouse link');
1849 SELECT db_link INTO l_instance from user_db_links where
1850 db_link like 'EDW_APPS_TO_WH%';
1851
1852
1853 execute immediate 'SELECT instance_code FROM edw_local_instance'
1854 INTO l_instance;
1855
1856 IF l_instance IS NULL OR l_instance =''
1857 then
1858 RAISE no_warehouse_link_found;
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN others THEN
1863 RAISE no_warehouse_link_found;
1864 END;
1865 --- We want one long call to get averything at once
1866 --- because this happens over a database link.
1867 --- --- DEBUG dbms_output.put_line('GENERATE_PRUNED_VIEW - Warehouse link exists');
1868 BEGIN
1869 OPEN l_FlexColRec_cur
1870 FOR selection_query_stmt
1871 using p_objectname, l_instance, p_viewname, p_objectname, l_instance;
1872
1873 --- --- DEBUG dbms_output.put_line('GENERATE_PRUNED_VIEW - Cursor Opened');
1874
1875 --- Get all names of selected flexfield segments for pruned view
1876 LOOP
1877 --- --- DEBUG
1878 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - looping');
1879 FETCH l_FlexColRec_cur INTO l_SubsetColRec;
1880 --- --- DEBUG
1881 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - fetching');
1882 EXIT WHEN l_FlexColRec_cur%NOTFOUND;
1883 --- --- DEBUG
1884 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - line fetched');
1885 l_subset_table.EXTEND;
1886 l_subset_table(l_subset_table.last) := l_SubsetColRec;
1887 --- --- DEBUG
1888 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - line stored');
1889 END LOOP;
1890
1891 --- --- DEBUG
1892 --- IF (l_subset_table.COUNT=0)
1893 --- THEN
1894 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - Cursor Done w/ NULL '
1895 --- );
1896 --- ELSE
1897 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - Cursor Done returning '
1898 --- || l_subset_table.last ||' lines ('
1899 --- || l_subset_table.count
1900 --- ||')' );
1901 --- END IF;
1902
1903
1904 --- Get all the names of base column tables to be appended to the view
1905 OPEN l_FlexColRec_cur
1906 FOR base_table_v_query_stmt
1907 using l_instance, p_viewname;
1908 --- --- DEBUG dbms_output.put_line('GENERATE_PRUNED_VIEW - Cursor Opened twice');
1909
1910 LOOP
1911 --- --- DEBUG dbms_output.put_line('GENERATE_PRUNED_VIEW - looping');
1912 FETCH l_FlexColRec_cur INTO l_SupersetColRec;
1913 --- --- DEBUG dbms_output.put_line('GENERATE_PRUNED_VIEW - fetching');
1914
1915 EXIT WHEN l_FlexColRec_cur%NOTFOUND;
1916 --- --- DEBUG
1917 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - line fetched');
1918
1919 --- dbms_output.put_line(l_SupersetColRec.table_alias
1920 --- ||'.'
1921 --- ||l_SupersetColRec.column_name);
1922 l_superset_table.EXTEND;
1923 l_superset_table(l_superset_table.last) := l_SupersetColRec;
1924 END LOOP;
1925
1926 --- --- DEBUG
1927 --- IF (l_superset_table.COUNT=0)
1928 --- THEN
1929 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - Cursor Done w/ NULL '
1930 --- );
1931 --- ELSE
1932 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - Cursor Done returning '
1933 --- || l_superset_table.last ||' lines ('
1934 --- || l_superset_table.count
1935 --- ||')' );
1936 --- END IF;
1937
1938 CLOSE l_FlexColRec_cur;
1939
1940 EXCEPTION WHEN OTHERS THEN NULL;
1941 END;
1942 --- --- DEBUG
1943 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - Cursor Closed');
1944
1945
1946
1947 --- --- Deleted check for no selection. Will behave as regular generate all
1948 --- --- if no columns are selected.
1949 --- IF (l_subset_table IS NULL OR l_subset_table.COUNT = 0)
1950 --- THEN
1951 --- RAISE no_columns_selected;
1952 --- END IF;
1953 --- ELSE process pruned view
1954
1955 BIS_VG_REPOSITORY_MEDIATOR.create_View_Text_Tables
1956 ( p_viewname
1957 , l_View_Column_Text_Table
1958 , l_view_select_text_table
1959 , l_dummy_tbl
1960 );
1961 --- ---DEBUG
1962 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - View Parsed');
1963 Update_View( p_view_column_text_table => l_view_column_text_table
1964 , p_view_select_text_table => l_View_Select_Text_Table
1965 , p_selected_columns => l_subset_table
1966 , p_extra_columns => l_superset_table
1967 , x_view_column_text_table => l_view_column_out_table
1968 , x_view_select_text_table => l_view_select_out_table
1969 , x_view_column_comment_table => l_view_column_comment_table
1970 , x_error_tbl => l_dummy_tbl
1971 );
1972 --- ---DEBUG
1973 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - View Processed');
1974 IF (l_view_column_out_table.COUNT > 0) THEN
1975 --create the view
1976 IF p_gen_viewname IS NULL
1977 THEN
1978 BIS_VG_COMPILE.write_View
1979 ( bis_vg_types.sqlplus_production_mode
1980 , p_viewname
1981 , l_View_Column_out_Table
1982 , l_View_Select_out_Table
1983 , l_View_Column_Comment_Table
1984 , l_View_Column_Comment_Table
1985 , l_dummy_tbl ----- not used
1986 );
1987 ELSE
1988 BIS_VG_COMPILE.write_View
1989 ( bis_vg_types.EDW_verify_mode
1990 , p_gen_viewname
1991 , l_View_Column_out_Table
1992 , l_View_Select_out_Table
1993 , l_View_Column_Comment_Table
1994 , l_View_Column_Comment_Table
1995 , l_dummy_tbl ----- not used
1996 );
1997 END IF;
1998 END IF;
1999 --- ---DEBUG
2000 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - View Created');
2001 ---- NOW record success in the database table
2002
2003 execute immediate update_status_stmt
2004 using
2005 'GENERATED_PRUNED',
2006 '',
2007 p_viewname;
2008 COMMIT;
2009
2010 --- dbms_output.put_line('GENERATE_PRUNED_VIEW - Status updated');
2011 EXCEPTION
2012 WHEN FND_API.g_exc_unexpected_error
2013 THEN
2014 --- bis_debug_pub.dumpdebug;
2015 bis_vg_log.write_error_to_string(l_error_string);
2016 execute immediate update_status_stmt
2017 using
2018 'FAILED_PRUNED',
2019 l_error_string,
2020 p_viewname;
2021 COMMIT;
2022
2023 --- RAISE; --- the same exception
2024 WHEN FND_API.g_exc_error
2025 THEN
2026 --- bis_debug_pub.dumpdebug;
2027 bis_vg_log.write_error_to_string(l_error_string);
2028 execute immediate update_status_stmt
2029 using
2030 'FAILED_PRUNED',
2031 l_error_string,
2032 p_viewname;
2033 COMMIT;
2034
2035 --- RAISE; --- the same exception
2036 WHEN OTHERS
2037 THEN
2038 --- bis_debug_pub.dumpdebug;
2039 l_error_string := 'New Error '|| SQLCODE||' : '|| SQLERRM;
2040 execute immediate update_status_stmt
2041 using
2042 'FAILED_PRUNED',
2043 l_error_string,
2044 p_viewname;
2045 COMMIT;
2046 --- RAISE; -- the same exception.
2047 END generate_pruned_view;
2048
2049
2050 -- ============================================================================
2051 --PROCEDURE : generate_Views
2052 --PARAMETERS: 1. x_error_buf error buffer to hold concurrent program
2053 -- errors
2054 -- 2. x_ret_code return code of concurrent program
2055 -- 3. p_all_flag generate all views for all products
2056 -- 4. p_Appl_Short_Name application product_short name
2057 -- 5. p_KF_Appl_Short_Name application product_short name
2058 -- 6. p_Key_Flex_Code key flexfield code
2059 -- 7. p_DF_Appl_Short_Name application product_short name
2060 -- 8. p_Desc_Flex_Name descriptive flex field name
2061 -- 9. p_Lookup_Table_Name lookup table name
2062 -- 10. p_Lookup_Type lookup code type
2063 -- 11. p_View_Name name of view to generate
2064 --COMMENT : Launch this program to generate the business view(s) with the
2065 -- key flexfield, descriptive flexfield and lookup information.
2066 --EXCEPTION : None
2067 -- ============================================================================
2068 PROCEDURE generate_Views -- PUBLIC PROCEDURE
2069 ( x_error_buf OUT VARCHAR2
2070 , x_ret_code OUT NUMBER
2071 , p_all_flag IN VARCHAR2 := NULL
2072 , p_App_Short_Name IN bis_vg_types.App_Short_Name_Type := NULL
2073 , p_KF_Appl_Short_Name IN bis_vg_types.App_Short_Name_Type := NULL
2074 , p_Key_Flex_Code IN bis_vg_types.Key_Flex_Code_Type := NULL
2075 , p_DF_Appl_Short_Name IN bis_vg_types.App_Short_Name_Type := NULL
2076 , p_Desc_Flex_Name IN bis_vg_types.Desc_Flex_Name_Type := NULL
2077 , p_Lookup_Table_Name IN VARCHAR2 := NULL
2078 , p_Lookup_Type IN bis_vg_types.Lookup_Code_Type := NULL
2079 , p_View_Name IN bis_vg_types.View_Name_Type := NULL
2080 )
2081 IS
2082 --
2083 l_View_Table bis_vg_types.View_Table_Type;
2084 l_View_Column_Text_Table bis_vg_types.View_Text_Table_Type;
2085 l_View_Select_Text_Table bis_vg_types.View_Text_Table_Type;
2086 l_View_Column_Out_Table bis_vg_types.View_Text_Table_Type;
2087 l_View_Select_Out_Table bis_vg_types.View_Text_Table_Type;
2088 l_View_Text_Table bis_vg_types.View_Text_Table_Type;
2089 l_View_Column_Comment_Table bis_vg_types.Flex_Column_Comment_Table_Type;
2090 l_debug_file VARCHAR2(2000);
2091 l_log_file VARCHAR2(2000);
2092 l_out_file VARCHAR2(2000);
2093 l_warehouse_exists NUMBER(1) ;
2094 l_instance VARCHAR2(200);
2095 l_generated_view_name bis_vg_types.view_name_type;
2096 l_error_message VARCHAR2(4000);
2097 l_return_status VARCHAR2(1000);
2098 l_error_Tbl BIS_VG_UTIL.Error_Tbl_Type;
2099
2100 --
2101 BEGIN
2102 --
2103 l_warehouse_exists := 1;
2104 IF ( g_mode = bis_vg_types.production_mode
2105 OR g_mode = bis_vg_types.sqlplus_production_mode
2106 OR g_mode = bis_vg_types.remove_tags_mode
2107 ) THEN
2108 fnd_profile.put('FND_AS_MSG_LEVEL_THRESHOLD'
2109 , FND_MSG_PUB.G_MSG_LVL_SUCCESS);
2110 ELSE
2111 fnd_profile.put('FND_AS_MSG_LEVEL_THRESHOLD'
2112 , FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
2113 END IF;
2114 --
2115 l_debug_file := bis_debug_pub.set_debug_mode('FILE');
2116 bis_debug_pub.initialize;
2117 bis_debug_pub.setdebuglevel(10);
2118 --
2119 bis_debug_pub.Add('> generate_Views');
2120 x_ret_code := 0;
2121 -- verify parameters
2122 -- retrieve business views
2123
2124 --- CHECK FOR WAREHOUSE PRESENCE
2125 BEGIN
2126
2127 execute immediate 'ALTER session SET global_names=FALSE';
2128 SELECT db_link INTO l_instance from user_db_links where
2129 db_link like 'EDW_APPS_TO_WH%';
2130
2131 SELECT a.table_name INTO l_instance FROM all_tables a, user_synonyms u
2132 WHERE a.table_name = 'EDW_LOCAL_GENERATION_STATUS'
2133 AND u.table_name= 'EDW_LOCAL_GENERATION_STATUS'
2134 AND a.owner = u.table_owner;
2135 l_warehouse_exists := 1;
2136 EXCEPTION
2137 WHEN others THEN
2138 l_warehouse_exists := 0;
2139 END;
2140
2141 BIS_VG_REPOSITORY_MEDIATOR.retrieve_Business_Views
2142 ( p_all_flag
2143 , p_App_Short_Name
2144 , p_KF_Appl_Short_Name
2145 , p_Key_Flex_Code
2146 , p_DF_Appl_Short_Name
2147 , p_Desc_Flex_Name
2148 , p_Lookup_Table_Name
2149 , p_Lookup_Type
2150 , p_View_Name
2151 , l_View_Table
2152 , l_return_status
2153 , l_error_Tbl
2154 );
2155 bis_vg_log.init_log(l_return_status, l_error_Tbl);
2156
2157 IF (g_mode <> bis_vg_types.production_mode) THEN
2158 --
2159 BEGIN
2160 -- only dbms_output allowed in the whole program
2161 --dbms_output.put_line('Debug file - ' || l_debug_file);
2162 BIS_VIEW_GENERATOR_PVT.g_debug_file := ('Debug file - ' || l_debug_file);
2163 EXCEPTION
2164 WHEN OTHERS THEN
2165 NULL;
2166 END;
2167
2168 --
2169 --
2170 IF (g_mode <> bis_vg_types.sqlplus_production_mode) THEN
2171 bis_debug_pub.debug_on;
2172 bis_debug_pub.Add('BIS_VEW_GENERATOR. Generate_Views : '
2173 || 'l_View_Table.count = ' || l_View_Table.COUNT);
2174 bis_debug_pub.debug_off;
2175 END IF;
2176 --
2177 END IF;
2178 --
2179 IF (l_View_Table.count = 0)
2180 THEN
2181 BIS_VG_UTIL.Add_Error_message
2182 ( p_error_msg_name => bis_view_generator_pvt.GENERATOR_NO_VIEWS
2183 , p_error_proc_name => G_PKG_NAME||'.generate_Views'
2184 , p_error_table => l_error_tbl
2185 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2186 , x_error_table => l_error_tbl
2187 );
2188 RAISE FND_API.G_EXC_ERROR;
2189 END IF;
2190
2191
2192 --
2193 --
2194 FOR i IN 1 .. l_View_Table.COUNT LOOP
2195 --
2196 fnd_msg_pub.initialize;
2197 l_generated_view_name :=
2198 bis_vg_util.get_generated_view_name (l_View_Table(i).view_name
2199 , l_return_status
2200 , l_error_Tbl
2201 );
2202 BEGIN
2203 --
2204 IF (g_mode <> bis_vg_types.test_no_view_gen_mode) then
2205 --- start a block for use in updating warehouse status
2206 BEGIN --- BLOCK inside IF enumerate-without-generate mode
2207 -- Bug 6819715
2208 -- Check the profile and database version to decide if the we need to modify some session and system settings
2209 --- and user optimizer hints
2210 BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints := nvl(FND_PROFILE.VALUE('BVG_OPTIMIZER_MODE'),'NEW');
2211 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2212 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2213 'fnd.plsql.BIS_VG_PVT.GENERATE_VIEWS',
2214 'g_use_optimizer_hints is '||g_use_optimizer_hints);
2215 END IF;
2216 BEGIN
2217 SELECT substrb(version, 1, instrb(version,'.') -1) into BIS_VIEW_GENERATOR_PVT.g_db_version
2218 FROM product_component_version
2219 WHERE upper(product) like 'ORACLE';
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 BIS_VIEW_GENERATOR_PVT.g_db_version := '10';
2223 END;
2224 IF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = '9' and BIS_VIEW_GENERATOR_PVT.g_db_version > 9 then
2225 --This is the only case we want to use optimizer hints to try to mimic a 9i database
2226 --when we are not on a 9i database
2227 BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints := '9.2';
2228 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2229 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2230 'fnd.plsql.BIS_VG_PVT.GENERATE_VIEWS',
2231 'g_use_optimizer_hints is 9.2');
2232 END IF;
2233
2234 ELSIF BIS_VIEW_GENERATOR_PVT.g_db_version IN ('10', '11') AND BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = 'NEW' then
2235 --This mode setting will cause the Desc Flex package to use the cursor with the new order by clause
2236 --on a new install no one should be on 9.2
2237 BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints := 'NEW';
2238 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2239 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2240 'fnd.plsql.BIS_VG_PVT.GENERATE_VIEWS',
2241 'g_use_optimizer_hints is NEW');
2242 END IF;
2243
2244 ELSIF BIS_VIEW_GENERATOR_PVT.g_db_version = '9' OR BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints IN ('10', '11') then
2245 --This setting cause the Desc Flex package to use the original cursor
2246 BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints := '9i';
2247 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2248 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2249 'fnd.plsql.BIS_VG_PVT.GENERATE_VIEWS',
2250 'g_use_optimizer_hints is 9i');
2251 END IF;
2252 END IF;
2253 IF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = '9.2'then
2254 -- execute immediate v_shared_pool;
2255 execute immediate v_session_sort;
2256 END IF;
2257 -- END BUG 6819715
2258 --- create two tables - one with columns, one with the view text
2259 BIS_VG_REPOSITORY_MEDIATOR.create_View_Text_Tables
2260 ( l_View_Table(i).view_name
2261 , l_View_Column_Text_Table
2262 , l_View_Select_Text_Table
2263 , l_error_Tbl
2264 );
2265 --
2266 Update_View( p_view_column_text_table => l_view_column_text_table
2267 , p_view_select_text_table => l_View_Select_Text_Table
2268 , p_mode => g_mode
2269 , x_view_column_text_table => l_view_column_out_table
2270 , x_view_select_text_table => l_view_select_out_table
2271 , x_view_column_comment_table => l_view_column_comment_table
2272 , x_error_tbl => l_error_Tbl
2273 );
2274
2275 --
2276 IF (l_view_column_out_table.COUNT > 0) THEN
2277 --create the view
2278 --- bis_debug_pub.debug_on;
2279 BIS_VG_COMPILE.write_View
2280 ( g_mode
2281 , l_View_Table(i).view_name
2282 , l_View_Column_out_Table
2283 , l_View_Select_out_Table
2284 , l_View_Column_Comment_Table
2285 , l_View_Column_Comment_Table
2286 --- , l_return_status
2287 , l_error_Tbl
2288 );
2289 --- bis_debug_pub.debug_off;
2290 --
2291 --
2292 -- Update the view column comments
2293 comment_Flex_Columns(l_generated_view_name
2294 , l_view_column_comment_table);
2295
2296 --- Update generate status for warehouse views
2297 IF ( l_warehouse_exists = 1
2298 AND
2299 (l_generated_view_name like '%LCV'
2300 OR l_generated_view_name like '%FCV'
2301 )
2302 )
2303 THEN
2304 BEGIN
2305 execute immediate update_status_stmt
2306 using
2307 'GENERATED_ALL',
2308 '',
2309 l_View_Table(i).view_name
2310 ;
2311
2312 IF SQL%notfound
2313 THEN
2314 execute immediate insert_status_stmt
2315 using
2316 l_View_Table(i).view_name,
2317 'GENERATED_ALL',
2318 ''
2319 ;
2320 END IF;
2321 EXCEPTION
2322 WHEN OTHERS THEN
2323 --- Ignore all exceptions due to non-definition of EDW
2324 l_warehouse_exists := 0;
2325 END;
2326
2327 END IF;
2328
2329
2330
2331 bis_vg_log.update_success_log( l_View_Table(i).view_name
2332 , l_generated_view_name
2333 , l_return_status
2334 , l_error_Tbl
2335 );
2336
2337 END IF; --- generated COUNT > 0
2338 EXCEPTION
2339 WHEN OTHERS THEN
2340 IF ( l_warehouse_exists = 1
2341 AND
2342 (l_generated_view_name like '%LCV'
2343 OR l_generated_view_name like '%FCV'
2344 )
2345 )
2346 THEN
2347 bis_vg_log.write_error_to_string(l_error_message);
2348 BEGIN
2349 execute immediate update_status_stmt
2350 using
2351 'FAILED_ALL',
2352 l_error_message,
2353 l_View_Table(i).view_name
2354 ;
2355 IF SQL%notfound
2356 THEN
2357 execute immediate insert_status_stmt
2358 using
2359 l_View_Table(i).view_name,
2360 'FAILED_ALL',
2361 l_error_message
2362 ;
2363 END IF;
2364 EXCEPTION
2365 WHEN OTHERS THEN
2366 --- Ignore all exceptions due to non-definition of EDW
2367 l_warehouse_exists := 0;
2368 END;
2369
2370 END IF;
2371 RAISE; --- the same excpetion to the next block
2372 END; --- BLOCK inside IF enumerate-without-generate mode
2373
2374 ELSE --- enumerate-without-generate mode
2375 bis_vg_log.update_success_log( l_View_Table(i).view_name
2376 , l_generated_view_name
2377 , l_return_status
2378 , l_error_Tbl
2379 );
2380
2381 END IF; --- Mode check
2382 --
2383 EXCEPTION
2384 --
2385 when FND_API.G_EXC_ERROR then
2386 bis_vg_log.backpatch_failure_log( l_View_Table(i).view_name
2387 , l_return_status
2388 , l_error_Tbl
2389 );
2390 l_return_status := FND_API.G_RET_STS_ERROR ;
2391 when FND_API.G_EXC_UNEXPECTED_ERROR then
2392 bis_vg_log.backpatch_failure_log( l_View_Table(i).view_name
2393 , l_return_status
2394 , l_error_Tbl
2395 );
2396 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2397 --
2398 WHEN OTHERS THEN
2399 bis_vg_log.update_failure_log( l_View_Table(i).view_name
2400 , SQLCODE
2401 , SQLERRM
2402 , l_return_status
2403 , l_error_Tbl
2404 );
2405 END;
2406 END LOOP;
2407 --
2408 bis_vg_log.write_log( g_mode
2409 , p_all_flag
2410 , p_App_Short_Name
2411 , p_KF_Appl_Short_Name
2412 , p_Key_Flex_Code
2413 , p_DF_Appl_Short_Name
2414 , p_Desc_Flex_Name
2415 , p_Lookup_Table_Name
2416 , p_Lookup_Type
2417 , p_View_Name
2418 , l_return_status
2419 , l_error_Tbl
2420 );
2421 --
2422 bis_debug_pub.Add('< generate_Views');
2423 --
2424 EXCEPTION
2425 WHEN FND_API.G_EXC_ERROR THEN
2426 bis_vg_log.update_failure_log( l_error_tbl
2427 , l_return_status
2428 , l_error_Tbl
2429 );
2430 bis_vg_log.backpatch_failure_log( 'N/A'
2431 , l_return_status
2432 , l_error_Tbl
2433 );
2434 bis_vg_log.write_log ( g_mode
2435 , p_all_flag
2436 , p_App_Short_Name
2437 , p_KF_Appl_Short_Name
2438 , p_Key_Flex_Code
2439 , p_DF_Appl_Short_Name
2440 , p_Desc_Flex_Name
2441 , p_Lookup_Table_Name
2442 , p_Lookup_Type
2443 , p_View_Name
2444 , l_return_status
2445 , l_error_Tbl
2446 );
2447 WHEN OTHERS THEN
2448 IF (g_mode = bis_vg_types.production_mode) THEN
2449 x_error_buf := SQLERRM;
2450 x_ret_code := 2;
2451 ELSE
2452 bis_debug_pub.debug_on;
2453 bis_debug_pub.Add('Error code - '||SQLCODE);
2454 bis_debug_pub.ADD('Error message - '||Sqlerrm);
2455 bis_debug_pub.debug_off;
2456 END IF;
2457 END generate_Views;
2458
2459 PROCEDURE set_mode
2460 (p_mode IN bis_vg_types.view_generator_mode_type)
2461 IS
2462 BEGIN
2463 bis_debug_pub.Add('> set_mode');
2464 g_mode := p_mode;
2465 bis_debug_pub.Add('< set_mode');
2466 EXCEPTION
2467 WHEN OTHERS THEN
2468 bis_debug_pub.debug_on;
2469 bis_debug_pub.add('bis_view_generator_pvt.set_mode');
2470 bis_debug_pub.debug_off;
2471 fnd_msg_pub.Add_Exc_Msg( 'bis_view_generator_pvt'
2472 , 'set_mode'
2473 );
2474 RAISE;
2475 --
2476 END set_mode;
2477
2478 PROCEDURE set_mode
2479 (p_mode IN bis_vg_types.view_generator_mode_type
2480 , x_return_status OUT VARCHAR2
2481 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
2482 )
2483 IS
2484 BEGIN
2485 bis_debug_pub.Add('> set_mode');
2486 x_return_status := FND_API.G_RET_STS_SUCCESS;
2487 g_mode := p_mode;
2488 bis_debug_pub.Add('< set_mode');
2489 --
2490 EXCEPTION
2491 when FND_API.G_EXC_ERROR then
2492 x_return_status := FND_API.G_RET_STS_ERROR ;
2493 RAISE FND_API.G_EXC_ERROR;
2494 when FND_API.G_EXC_UNEXPECTED_ERROR then
2495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2497 when others then
2498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2499 BIS_VG_UTIL.Add_Error_Message
2500 ( p_error_msg_id => SQLCODE
2501 , p_error_description => SQLERRM
2502 , p_error_proc_name => G_PKG_NAME||'.set_mode'
2503 , p_error_table => x_error_tbl
2504 , x_error_table => x_error_tbl
2505 );
2506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2507 END set_mode;
2508
2509 END bis_view_generator_pvt;
2510