[Home] [Help]
PACKAGE BODY: APPS.BIS_VG_COMPILE
Source
4 -- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
1 PACKAGE BODY bis_vg_compile AS
2 /* $Header: BISTCMPB.pls 120.6 2008/04/23 17:11:18 dbowles ship $ */
3
5 -- All rights reserved.
6 --
7 -- FILENAME
8 --
9 -- BISTCMPB.pls
10 --
11 -- DESCRIPTION
12 --
13 -- body of package which writes the business views
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 --
19 -- 21-AUG-98 ANSINGHA created
20 -- 12-MAY-99 WNASRALL replaced call to do_array_ddl with do_ddl for eficiency
21 -- 06-APR-01 DBOWLES modified make_column_len30 and write view procedures. Add two
22 -- new parameters of bis_vg_types.Flex_Column_Comment_Table_Type
23 -- to each procedure.
24 -- 25-OCT-05 Edited by donald.bowles Made changes for NOCOPY hint.
25 --
26 --
27 G_PKG_NAME CONSTANT VARCHAR(30) := 'bis_vg_compile';
28
29 --=====================
30 --PRIVATE TYPES
31 --=====================
32 --
33 -- ============================================================================
34 --TYPE : View_Text_Count_Rec_Type
35 -- ============================================================================
39 , Count NUMBER
36 TYPE View_Text_Count_Rec_Type IS -- local type
37 RECORD
38 ( Column_name bis_vg_types.View_Text_Table_Rec_Type
40 );
41
42 --
43 --============================================================================
44 --TYPE : View_Text_Count_Table_Type
45 --============================================================================
46 TYPE View_Text_Count_Table_Type IS
47 -- local type
48 TABLE OF
49 View_Text_Count_Rec_Type INDEX BY BINARY_INTEGER;
50 --
51 --
52 --=====================
53 --OBSOLETE PROCEDURES
54 --=====================
55 --
56 --
57 --============================================================================
58 --PROCEDURE : remove_blank_lines
59 --PARAMETERS: 1. p_View_Text_Table table of varchars for view text
60 -- 2. x_View_Text_Table table of varchars for view text
61 -- 3. x_return_status error or normal
62 -- 4. x_error_Tbl table of error messages
63 --COMMENT : Call this procedure to remove blank lines from the view table
64 --EXCEPTION : None
65 --============================================================================
66 --PROCEDURE remove_blank_lines ( p_view_text_table IN
67 --bis_vg_types.view_text_table_type , x_view_text_table out
68 --bis_vg_types.view_text_table_type , x_return_status OUT VARCHAR2 ,
69 --x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type ) IS l_str
70 --bis_vg_types.view_text_table_rec_type; l_char VARCHAR(1); l_length NUMBER;
71 --BEGIN
72 -- bis_debug_pub.Add('> remove_blank_lines');
73 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
74 --
75 -- FOR i IN 1 .. p_view_text_table.COUNT LOOP
76 -- l_str := p_view_text_table(i);
77 -- l_length := Length(l_str);
78 -- IF (l_length > 0) THEN
79 -- FOR j IN 1 .. l_length LOOP
80 -- l_char := Substr(l_str, j, 1);
81 -- IF (l_char <> ' ' AND l_char <> ' ') THEN
82 -- x_view_text_table(x_view_text_table.COUNT + 1) := l_str;
83 -- EXIT;
84 -- END IF;
85 -- END LOOP;
86 -- END IF;
87 -- END LOOP;
88 --
89 -- bis_debug_pub.Add('< remove_blank_lines');
90 --
91 --
92 --EXCEPTION
93 -- when FND_API.G_EXC_ERROR then
94 -- x_return_status := FND_API.G_RET_STS_ERROR ;
95 -- RAISE FND_API.G_EXC_ERROR;
96 -- when FND_API.G_EXC_UNEXPECTED_ERROR then
97 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
98 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 -- when others then
100 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
101 -- BIS_VG_UTIL.Add_Error_Message
102 -- ( p_error_msg_id => SQLCODE
103 -- , p_error_description => SQLERRM
104 -- , p_error_proc_name => G_PKG_NAME||'.remove_blank_lines'
105 -- , p_error_table => x_error_tbl
106 -- , x_error_table => x_error_tbl
107 -- );
108 -- bis_vg_log.update_failure_log( x_error_tbl
109 -- , x_return_status
110 -- , x_error_Tbl
111 -- );
112 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 --
114 --END remove_blank_lines;
115 --
116 -- ============================================================================
117 --PROCEDURE : create_DSQL_view
118 --PARAMETERS: 1. p_View_Text_Table complete view text table
119 -- 2. x_return_status error or normal
120 -- 3. x_error_Tbl table of error messages
121 --COMMENT : Call this function to retrieve the application short name
122 -- for a particular application.
123 --RETURN : application short name
124 --EXCEPTION : None
125 -- ============================================================================
126 --PROCEDURE create_DSQL_view
127 --( p_View_Text_Table IN bis_vg_types.View_Text_Table_Type
128 --, x_return_status OUT VARCHAR2
129 --, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
130 --)
131 -- IS
132 ----
133 -- p_CursorID INTEGER;
134 -- p_SQL_text LONG := null;
135 -- p_dummy INTEGER;
136 ----
137 -- BEGIN
138 -- bis_debug_pub.Add('> create_DSQL_view');
139 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
140 -- FOR i IN 1 .. p_View_Text_Table.COUNT LOOP
141 -- p_SQL_text := p_SQL_text || p_View_Text_Table(i);
142 -- END LOOP;
143 -- p_CursorID := DBMS_SQL.OPEN_CURSOR;
144 -- DBMS_SQL.PARSE( p_CursorID, p_SQL_text, DBMS_SQL.NATIVE);
145 -- p_dummy := DBMS_SQL.EXECUTE(p_CursorID);
146 -- bis_debug_pub.Add('< create_DSQL_view');
147 --
148 --
149 -- EXCEPTION
150 -- when FND_API.G_EXC_ERROR then
151 -- x_return_status := FND_API.G_RET_STS_ERROR ;
152 -- RAISE FND_API.G_EXC_ERROR;
153 -- when FND_API.G_EXC_UNEXPECTED_ERROR then
154 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
155 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156 -- when others then
157 --
158 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
159 -- BIS_VG_UTIL.Add_Error_Message
160 -- ( p_error_msg_id => SQLCODE
161 -- , p_error_description => SQLERRM
162 -- , p_error_proc_name => G_PKG_NAME||'.create_DSQL_view'
163 -- , p_error_table => x_error_tbl
164 -- , x_error_table => x_error_tbl
165 -- );
166 -- bis_vg_log.update_failure_log( x_error_tbl
167 -- , x_return_status
168 -- , x_error_Tbl
169 -- );
170 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171 --
172 -- END create_DSQL_view;
173 -- ============================================================================
174 --PROCEDURE : execute_DDL_Statement
178 -- 4. x_error_Tbl table of error messages
175 --PARAMETERS: 1. p_View_Table_Rec name of view to be created
176 -- 2. p_ub last statement in buffer
177 -- 3. x_return_status error or normal
179 --
180 --COMMENT : Call this procedure to execute the dynamic SQL statement to
181 -- create the view.
182 --EXCEPTION : None
183 -- ============================================================================
184 --PROCEDURE execute_DDL_Statement -- PRIVATE PROCEDURE
185 --( p_View_Table_Rec IN bis_vg_types.View_Table_Rec_Type
186 --, p_ub IN INTEGER
187 --, x_return_status OUT VARCHAR2
188 --, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
189 --)
190 --IS
191 ----
192 --l_applsys_schema VARCHAR2(100);
193 --l_application_short_name VARCHAR2(10);
194 --l_View_Name VARCHAR2(100);
195 --dummy1 VARCHAR2(2);
196 --dummy2 VARCHAR2(2);
197 --l_lb INTEGER := 1;
198 --l_retvar BOOLEAN;
199 ----
200 --BEGIN
201 -- bis_debug_pub.Add('> execute_DDL_Statement');
202 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
203 -- l_retvar := FND_INSTALLATION.GET_APP_INFO
204 -- ( 'FND'
205 -- , dummy1
206 -- , dummy2
207 -- , l_applsys_schema
208 -- );
209 -- bis_debug_pub.Add('l_applsys_schema = ' || l_applsys_schema);
210 -- bis_debug_pub.Add('p_View_Table_Rec.View_Name = '
211 -- || p_View_Table_Rec.View_Name);
212 --*************************************************************************
213 -- BIG COMMENT
214 -- force AD_DDL to behave like a single instance as we want the view
215 -- to be created only once; here FND has been hardcoded for this purpose
216 --*************************************************************************
217 -- l_application_short_name := 'FND';
218 -- l_View_Name := p_View_Table_Rec.View_Name;
219 --
220 -- bis_debug_pub.Add(' l_applsys_schema = '||l_applsys_schema);
221 -- bis_debug_pub.ADD(' l_application_short_name = '||l_application_short_name);
222 -- bis_debug_pub.ADD(' ad_ddl.create_view = '||ad_ddl.create_view);
223 -- bis_debug_pub.ADD(' l_lb = '||l_lb);
224 -- bis_debug_pub.ADD(' p_ub = '||p_ub);
225 -- bis_debug_pub.ADD(' l_View_Name = '||l_View_Name);
226 --
227 -- AD_DDL.do_array_ddl( l_applsys_schema
228 -- , l_application_short_name
229 -- , ad_ddl.create_view
230 -- , l_lb
231 -- , p_ub
232 -- , l_View_Name
233 -- );
234 --
235 -- bis_debug_pub.Add('< execute_DDL_Statement');
236 ----
237 --
238 --
239 --EXCEPTION
240 -- when FND_API.G_EXC_ERROR then
241 -- x_return_status := FND_API.G_RET_STS_ERROR ;
242 -- RAISE FND_API.G_EXC_ERROR;
243 -- when FND_API.G_EXC_UNEXPECTED_ERROR then
244 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
246 -- when others then
247 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
248 -- BIS_VG_UTIL.Add_Error_Message
249 -- ( p_error_msg_id => SQLCODE
250 -- , p_error_description => SQLERRM
251 -- , p_error_proc_name => G_PKG_NAME||'.execute_DDL_Statement'
252 -- , p_error_table => x_error_tbl
253 -- , x_error_table => x_error_tbl
254 -- );
255 -- bis_vg_log.update_failure_log( x_error_tbl
256 -- , x_return_status
257 -- , x_error_Tbl
258 -- );
259 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 --
261 --END execute_DDL_Statement;
262 --
263 --
264 --
265 -- ============================================================================
266 --PROCEDURE : build_DDL_Statement
267 --PARAMETERS: 1. p_View_Text_Table table of varchars to hold view creation
268 -- text
269 -- 2. x_return_status error or normal
270 -- 3. x_error_Tbl table of error messages
271 --COMMENT : Call this procedure to create a DDL statement from the view text
272 -- of the view.
273 --EXCEPTION : None
274 -- ============================================================================
275 --PROCEDURE build_DDL_Statement -- PRIVATE PROCEDURE
276 --( p_View_Text_Table IN bis_vg_types.View_Text_Table_Type
277 --, x_return_status OUT VARCHAR2
278 --, x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
279 --)
280 --IS
281 --
282 --l_count INTEGER;
283 --
284 --BEGIN
285 -- bis_debug_pub.Add('> build_DDL_Statement');
286 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
287 -- FOR l_count IN 1 .. p_View_Text_Table.COUNT LOOP
288 -- AD_DDL.build_statement(' '||p_View_Text_Table(l_count)||' ', l_count);
289 -- END LOOP;
290 -- bis_debug_pub.Add('< build_DDL_Statement');
291 --
292 --
293 --EXCEPTION
294 -- when FND_API.G_EXC_ERROR then
295 -- x_return_status := FND_API.G_RET_STS_ERROR ;
296 -- RAISE FND_API.G_EXC_ERROR;
297 -- when FND_API.G_EXC_UNEXPECTED_ERROR then
298 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
299 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 -- when others then
301 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
302 -- BIS_VG_UTIL.Add_Error_Message
303 -- ( p_error_msg_id => SQLCODE
304 -- , p_error_description => SQLERRM
308 -- );
305 -- , p_error_proc_name => G_PKG_NAME||'.build_DDL_Statement'
306 -- , p_error_table => x_error_tbl
307 -- , x_error_table => x_error_tbl
309 -- bis_vg_log.update_failure_log( x_error_tbl
310 -- , x_return_status
311 -- , x_error_Tbl
312 -- );
313 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
314 --
315 --END build_DDL_Statement;
316
317 --=====================
318 --PRIVATE PROCEDURES
319 --=====================
320 --
321 -- ============================================================================
322 --PROCEDURE : do_long_DDL
323 --PARAMETERS:
324 -- 1. p_View_Table_Rec record of view table
325 -- 2. p_View_Create_Text_Table table of varchars for create view text
326 -- 3. p_View_Select_Text_Table table of varchars for select view text
327 -- 4. p_applsys_schema name of schema tobe used in call to ad_ddl
328 -- 5. x_return_status error or normal
329 -- 6. x_error_Tbl table of error messages
330 --COMMENT : Creates a DDL statement from the tables directly.
331 -- Best used whenthe view text is bigger than 30K.
332 --EXCEPTION : None
333 -- ============================================================================
334 PROCEDURE DO_LONG_DDL
335 ( p_mode IN NUMBER
336 , p_view_name IN VARCHAR2
337 , p_View_Create_Text_Table IN bis_vg_types.View_Text_Table_Type
338 , p_View_Select_Text_Table IN bis_vg_types.View_Text_Table_Type
339 , p_applsys_schema IN VARCHAR2
340 , x_return_status OUT NOCOPY VARCHAR2
341 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
342 )
343
344 IS
345 l_count NUMBER;
346 l_total NUMBER;
347 l_dummy VARCHAR2(100);
348 BEGIN
349 bis_debug_pub.Add('> do_long_ddl');
350 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352
353 FOR l_count IN 1 .. p_View_Create_Text_Table.COUNT
354 LOOP
355 AD_DDL.build_statement(p_View_Create_Text_Table(l_count)
356 , l_count);
357 IF (p_mode <> bis_vg_types.production_mode AND
358 p_mode <> bis_vg_types.sqlplus_production_mode) THEN
359 BIS_DEBUG_PUB.Add(p_View_Create_Text_Table(l_count));
360 END IF;
361 l_total := l_count;
362 END LOOP;
363
364 FOR l_count IN 1 .. p_View_Select_Text_Table.COUNT
365 LOOP
366 l_total := l_total+1;
367 AD_DDL.build_statement(p_View_Select_Text_Table(l_count)
368 , l_total);
369 IF (p_mode <> bis_vg_types.production_mode AND
370 p_mode <> bis_vg_types.sqlplus_production_mode) THEN
371 BIS_DEBUG_PUB.Add(p_View_Select_Text_Table(l_count));
372 END IF;
373
374 END LOOP;
375 AD_DDL.do_array_ddl( p_applsys_schema
376 , 'FND' -- hardcoded to force AD_DDL
377 -- to create the view only once
378 , ad_ddl.create_view -- type
379 , 1
380 , l_total
381 , p_View_Name
382 );
383 bis_debug_pub.Add('< do_long_ddl');
384
385 EXCEPTION
386 when FND_API.G_EXC_ERROR then
387 --- x_return_status := FND_API.G_RET_STS_ERROR ;
388 RAISE FND_API.G_EXC_ERROR;
389 when FND_API.G_EXC_UNEXPECTED_ERROR then
390 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
392 when others then
393 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
394 BIS_VG_UTIL.Add_Error_Message
395 ( p_error_msg_id => SQLCODE
396 , p_error_description => SQLERRM||'[ '||ad_ddl.error_buf||' ]'
397 , p_error_proc_name => G_PKG_NAME||'.do_long_ddl'
398 , p_error_table => x_error_tbl
399 , x_error_table => x_error_tbl
400 );
401 bis_vg_log.update_failure_log( x_error_tbl
402 , x_return_status
403 , x_error_Tbl
404 );
405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
406
407 END do_long_ddl;
408 --
409 -- ============================================================================
410 --PROCEDURE : do_short_DDL
411 --PARAMETERS:
412 -- 1. p_View_name name of view table
413 -- 2. p_View_Create_Text_Table table of varchars for create view text
414 -- 3. p_View_Select_Text_Table table of varchars for select view text
415 -- 4. p_applsys_schema name of schema tobe used in call to ad_ddl
416 -- 5. x_return_status error or normal
417 -- 6. x_error_Tbl table of error messages
418 --COMMENT : Creates a DDL statement using a 28K vcarchar2.
419 -- Best used whenthe view text is smaller than 28K.
420 --EXCEPTION : Throws 'expected_overflow_exception' to indicate text overflow
421 -- (i.e. view text bigger than 30K)
422 -- ============================================================================
423 PROCEDURE DO_SHORT_DDL
424
425 ( p_mode IN NUMBER
426 , p_view_name IN VARCHAR2
427 , p_View_Create_Text_Table IN bis_vg_types.View_Text_Table_Type
428 , p_View_Select_Text_Table IN bis_vg_types.View_Text_Table_Type
429 , p_applsys_schema IN VARCHAR2
430 , x_return_status OUT NOCOPY VARCHAR2
431 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
432 )
433
434 IS
435 l_buffer VARCHAR2(29000);
436 l_count NUMBER;
437 l_length NUMBER := 0;
438 BEGIN
439 bis_debug_pub.Add('> do_short_ddl');
440 x_return_status := FND_API.G_RET_STS_SUCCESS;
441
442
443 BEGIN
444 FOR l_count IN 1 .. p_View_Create_Text_Table.COUNT
445 LOOP
446 l_length := l_length + length(p_View_Create_Text_Table(l_count));
447 if l_length > 30999 then raise expected_overflow_error;
448 end if;
449 IF (p_mode <> bis_vg_types.production_mode AND
450 p_mode <> bis_vg_types.sqlplus_production_mode) THEN
451 BIS_DEBUG_PUB.Add(p_View_Create_Text_Table(l_count));
452 END IF;
453 l_buffer:=l_buffer || p_View_Create_Text_Table(l_count);
454 END LOOP;
455
456 FOR l_count IN 1 .. p_View_Select_Text_Table.COUNT
457 LOOP
458 l_buffer:=l_buffer || p_View_Select_Text_Table(l_count);
459 l_length := l_length + length(p_View_Select_Text_Table(l_count));
460 --- if l_length > 30999 then raise expected_overflow_error;
461 --- end if;
462 IF (p_mode <> bis_vg_types.production_mode AND
463 p_mode <> bis_vg_types.sqlplus_production_mode) THEN
464 BIS_DEBUG_PUB.Add(p_View_Select_Text_Table(l_count) );
465 END IF;
466 END LOOP;
467
468 IF l_buffer IS NULL
469 THEN RAISE expected_overflow_error;
470 END IF;
471 EXCEPTION
472 when numeric_or_value_error then
473 -- This is propagated because it is expected
474 bis_debug_pub.Add('Failed do_short');
475 raise expected_overflow_error;
476 when expected_overflow_error then
477 raise;
478
479 END;
480
481 AD_DDL.DO_DDL( p_applsys_schema
482 , 'FND' -- hardcoded to force AD_DDL
483 -- to create the view only once
484 , ad_ddl.create_view -- type
485 , l_buffer
486 , p_View_Name
487 );
488 bis_debug_pub.Add('< do_short_ddl');
489
490 EXCEPTION
491 when expected_overflow_error
492 -- This is propagated because it is expected
493 then
494 bis_debug_pub.Add('Expected exit from do_short_ddl');
495 raise;
496 when numeric_or_value_error then
497 bis_debug_pub.Add('Numeric_or_value error unexpected in do_short_ddl');
498 RAISE;
499 when FND_API.G_EXC_ERROR then
500 x_return_status := FND_API.G_RET_STS_ERROR ;
501 RAISE FND_API.G_EXC_ERROR;
502 when FND_API.G_EXC_UNEXPECTED_ERROR then
503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 when others then
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
507 BIS_VG_UTIL.Add_Error_Message
508 ( p_error_msg_id => SQLCODE
509 , p_error_description => SQLERRM||'[ '||ad_ddl.error_buf||' ]'
510 , p_error_proc_name => G_PKG_NAME||'.do_short_ddl'
511 , p_error_table => x_error_tbl
512 , x_error_table => x_error_tbl
513 );
514 bis_vg_log.update_failure_log( x_error_tbl
515 , x_return_status
516 , x_error_Tbl
517 );
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
519
520 END do_short_ddl;
521
522
523
524 --
525 -- ============================================================================
526 --PROCEDURE : make_column_len30
527 --PARAMETERS: 1. p_View_Column_Table table of varchars
528 -- 2. x_View_Column_Table table of varchars
529 -- 3. x_return_status error or normal
530 -- 4. x_error_Tbl table of error messages
531 --COMMENT : Call this procedure to ensure column names <= 30 bytes
532 --EXCEPTION : None
533 -- ============================================================================
534 PROCEDURE make_column_len30 -- PRIVATE PROCEDURE
535 ( p_View_Column_Table IN bis_vg_types.View_Text_Table_Type
536 , p_View_Column_Comment_Table IN bis_vg_types.Flex_Column_Comment_Table_Type
537 , x_View_Column_Table IN OUT NOCOPY bis_vg_types.View_Text_Table_Type
538 , x_View_Column_Comment_Table IN OUT NOCOPY bis_vg_types.Flex_Column_Comment_Table_Type
539 , x_return_status OUT NOCOPY VARCHAR2
540 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
541 )
542 IS
543 --
544 l_column_name bis_vg_types.View_Text_Table_Rec_Type;
545 l_start_string bis_vg_types.View_Text_Table_Rec_Type;
546 l_end_string bis_vg_types.View_Text_Table_Rec_Type;
547 l_original_column_name bis_vg_types.View_Text_Table_Rec_Type;
548 l_pos NUMBER;
549 --
550 BEGIN
551 bis_debug_pub.Add('> make_column_len30');
552 x_return_status := FND_API.G_RET_STS_SUCCESS;
553 x_View_Column_Table := p_View_Column_Table;
554 x_View_Column_Comment_Table := p_View_Column_Comment_Table;
555 FOR i IN 1 .. p_View_Column_Table.COUNT LOOP
559 IF(LENGTHB(l_column_name) > 30) THEN
556 l_column_name := p_View_Column_Table(i);
557 l_original_column_name := l_column_name;
558 --
560 l_pos := INSTRB(l_column_name, '^');
561 IF(l_pos = 0) THEN
562 l_column_name := SUBSTRB(l_column_name, 1, 30);
563 --SUBSTRB seems to pull extra bytes if the last character is multibyte.
564 --testing new column length and reducing the number of bytes requested
565 --if length still over 30 bytes.
566 IF (LENGTHB(l_column_name) > 30) THEN
567 l_column_name := SUBSTRB(l_column_name, 1, 29);
568 IF (LENGTHB(l_column_name) > 30) THEN
569 l_column_name := SUBSTRB(l_column_name, 1, 28);
570 END IF;
571 END IF;
572 ELSE
573 l_start_string := SUBSTRB(l_column_name, 1, l_pos - 1);
574 l_end_string := SUBSTRB(l_column_name, l_pos + 1);
575 l_column_name := SUBSTRB(l_start_string, 1, 29 - LENGTHB(l_end_string))
576 || '^' || l_end_string;
577 IF (LENGTHB(l_column_name) > 30) THEN
578 l_column_name := SUBSTRB(l_column_name, 1, 29);
579 IF (LENGTHB(l_column_name) > 30) THEN
580 l_column_name := SUBSTRB(l_column_name, 1, 28);
581 END IF;
582 END IF;
583 END IF;
584 x_View_Column_Table(i) := l_column_name;
585 <<comment_loop>>
586 FOR j IN 1 .. p_View_Column_Comment_Table.COUNT LOOP
587 IF (x_View_Column_Comment_Table(j).column_name = l_original_column_name) THEN
588 x_View_Column_Comment_Table(j).column_name := l_column_name;
589 EXIT comment_loop;
590 END IF;
591 END LOOP comment_loop;
592
593 END IF;
594 END LOOP;
595 bis_debug_pub.Add('< make_column_len30');
596
597 EXCEPTION
598 when FND_API.G_EXC_ERROR then
599 x_return_status := FND_API.G_RET_STS_ERROR ;
600 RAISE FND_API.G_EXC_ERROR;
601 when FND_API.G_EXC_UNEXPECTED_ERROR then
602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 when others then
605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
606 BIS_VG_UTIL.Add_Error_Message
607 ( p_error_msg_id => SQLCODE
608 , p_error_description => SQLERRM
609 , p_error_proc_name => G_PKG_NAME||'.make_column_len30'
610 , p_error_table => x_error_tbl
611 , x_error_table => x_error_tbl
612 );
613 bis_vg_log.update_failure_log( x_error_tbl
614 , x_return_status
615 , x_error_Tbl
616 );
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618
619
620 END make_column_len30;
621 --
622 -- ============================================================================
623 --FUNCTION : find_column
624 --PARAMETERS: 1. p_column_name column name to look for
625 -- 2. p_View_Text_Count_Table table of View_Text_Count_Rec_Type
626 -- 3. x_return_status error or normal
627 -- 4. x_error_Tbl table of error messages
628 --RETURN : NUMBER
629 --COMMENT : Call this function to get the index of p_column_name in
630 -- p_View_Text_Count_Table; else return 0;
631 --EXCEPTION : None
632 -- ============================================================================
633 FUNCTION find_column -- PRIVATE FUNCTION
634 ( p_column_name IN bis_vg_types.View_Text_Table_Rec_Type
635 , p_View_Text_Count_Table IN View_Text_Count_Table_Type
636 , x_return_status OUT NOCOPY VARCHAR2
637 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
638 )
639 RETURN NUMBER IS
640 --
641 BEGIN
642 bis_debug_pub.Add('> find_column');
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644 FOR i IN 1 .. p_View_Text_Count_Table.COUNT LOOP
645 IF( REPLACE(p_column_name, '^', '_') =
646 REPLACE(p_View_Text_Count_Table(i).Column_name, '^', '_') ) THEN
647 RETURN i;
648 END IF;
649 END LOOP;
650 bis_debug_pub.Add('< find_column');
651 RETURN 0;
652
653
654
655 EXCEPTION
656 when FND_API.G_EXC_ERROR then
657 x_return_status := FND_API.G_RET_STS_ERROR ;
658 RAISE FND_API.G_EXC_ERROR;
659 when FND_API.G_EXC_UNEXPECTED_ERROR then
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
662 when others then
663 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
664 BIS_VG_UTIL.Add_Error_Message
665 ( p_error_msg_id => SQLCODE
666 , p_error_description => SQLERRM
667 , p_error_proc_name => G_PKG_NAME||'.find_column'
668 , p_error_table => x_error_tbl
669 , x_error_table => x_error_tbl
670 );
671 bis_vg_log.update_failure_log( x_error_tbl
672 , x_return_status
673 , x_error_Tbl
674 );
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676
677 END find_column;
678 --
679 -- ============================================================================
680 --PROCEDURE : insert_hat_in_column
681 --PARAMETERS: 1. p_column_name column name
682 -- 2. p_table_column_name column name in count table
683 -- 3. x_column_name updated column name
684 -- 4. x_return_status error or normal
685 -- 5. x_error_Tbl table of error messages
686 --COMMENT : Call this procedure to put hat in p_column_name if
687 -- p_table_column_name has a hat
688 --EXCEPTION : None
689 -- ============================================================================
690 PROCEDURE insert_hat_in_column -- PRIVATE PROCEDURE
691 ( p_column_name IN bis_vg_types.View_Text_Table_Rec_Type
692 , p_table_column_name IN bis_vg_types.View_Text_Table_Rec_Type
693 , x_column_name IN OUT NOCOPY bis_vg_types.View_Text_Table_Rec_Type
694 , x_return_status OUT NOCOPY VARCHAR2
695 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
696 )
697 IS
698 --
699 l_pos NUMBER;
700 --
701 BEGIN
702 bis_debug_pub.Add('> insert_hat_in_column');
703 x_return_status := FND_API.G_RET_STS_SUCCESS;
704 x_column_name := p_column_name;
705 l_pos := INSTRB(p_table_column_name, '^');
706 IF(l_pos > 0) THEN
707 x_column_name := SUBSTRB(p_column_name, 1, l_pos - 1) ||
708 '^' ||
709 SUBSTRB(p_column_name, l_pos + 1);
710 END IF;
711 bis_debug_pub.Add('< insert_hat_in_column');
712
713
714 EXCEPTION
715 when FND_API.G_EXC_ERROR then
716 x_return_status := FND_API.G_RET_STS_ERROR ;
717 RAISE FND_API.G_EXC_ERROR;
718 when FND_API.G_EXC_UNEXPECTED_ERROR then
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
721 when others then
722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
723 BIS_VG_UTIL.Add_Error_Message
724 ( p_error_msg_id => SQLCODE
725 , p_error_description => SQLERRM
726 , p_error_proc_name => G_PKG_NAME||'.insert_hat_in_column'
727 , p_error_table => x_error_tbl
728 , x_error_table => x_error_tbl
729 );
730 bis_vg_log.update_failure_log( x_error_tbl
731 , x_return_status
732 , x_error_Tbl
733 );
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735
736 END insert_hat_in_column;
737 --
738 -- ============================================================================
739 --PROCEDURE : make_unique_columns
740 --PARAMETERS: 1. p_View_Column_Table table of varchars
741 -- 2. p_View_Column_Comment_Table Table of records
742 -- holding flex info for flex derived columns
743 -- 3. x_View_Column_Table table of varchars
744 -- 4. x_View_Column_Comment_Table table of records
745 -- holding flex info for flex derived columns
746 -- 5. x_return_status error or normal
747 -- 6. x_error_Tbl table of error messages
748 --COMMENT : Call this procedure to ensure column names are unique
749 --EXCEPTION : None
750 -- ============================================================================
751 PROCEDURE make_unique_columns -- PRIVATE PROCEDURE
752 ( p_View_Column_Table IN bis_vg_types.View_Text_Table_Type
753 , p_View_Column_Comment_Table IN bis_vg_types.Flex_Column_Comment_Table_Type
754 , x_View_Column_Table IN OUT NOCOPY bis_vg_types.View_Text_Table_Type
755 , x_View_Column_Comment_Table IN OUT NOCOPY bis_vg_types.Flex_Column_Comment_Table_Type
756 , x_return_status OUT NOCOPY VARCHAR2
757 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
758 )
759 IS
760 --
761 l_View_Text_Count_Table View_Text_Count_Table_Type;
762 l_View_Text_Count_Rec View_Text_Count_Rec_Type;
763 l_column_name bis_vg_types.View_Text_Table_Rec_Type;
764 l_count_string VARCHAR2(10);
765 l_end_string VARCHAR2(100);
766 l_start_string VARCHAR2(100);
767 l_index NUMBER;
768 l_pos NUMBER;
769 --
770 BEGIN
771 bis_debug_pub.Add('> make_unique_columns');
772 x_return_status := FND_API.G_RET_STS_SUCCESS;
773 x_View_Column_Table := p_View_Column_Table;
774 x_View_Column_Comment_Table := p_View_Column_Comment_Table;
775 FOR i IN 1 .. x_View_Column_Table.COUNT LOOP
776 x_View_Column_Table(i) := UPPER(x_View_Column_Table(i));
777 l_column_name := x_View_Column_Table(i);
778 l_index := find_column( l_column_name
779 , l_View_Text_Count_Table
780 , x_return_status
781 , x_error_Tbl
782 );
783 IF(l_index = 0) THEN
784 l_View_Text_Count_Rec.Column_name := l_column_name;
785 l_View_Text_Count_Rec.Count := 0;
786 l_View_Text_Count_Table(l_View_Text_Count_Table.COUNT + 1)
787 := l_View_Text_Count_Rec;
788 ELSE
789 bis_debug_pub.add('l_column_name = ' || l_column_name);
790 bis_debug_pub.add('l_index = ' || l_index);
791 bis_debug_pub.add('l_View_Text_Count_Table(l_index).Column_name = '
792 || l_View_Text_Count_Table(l_index).Column_name);
793 bis_debug_pub.add('l_View_Text_Count_Table(l_index).Count = '
794 || l_View_Text_Count_Table(l_index).Count);
795 l_View_Text_Count_Table(l_index).Count
796 := l_View_Text_Count_Table(l_index).Count + 1;
797 l_count_string := TO_CHAR(l_View_Text_Count_Table(l_index).Count);
798 insert_hat_in_column( l_column_name
799 , l_View_Text_Count_Table(l_index).Column_name
800 , l_column_name
801 , x_return_status
802 , x_error_Tbl
803 );
804 x_View_Column_Table(i) := l_column_name || '^' || l_count_string;
805 END IF;
806 -- column comment table needs to have its column names match the new column table name
807 <<comment_loop>>
808 FOR j IN 1 .. x_View_Column_Comment_Table.COUNT LOOP
809 IF ( UPPER(x_View_Column_Comment_Table(j).column_name) = l_column_name ) THEN
810 x_View_Column_Comment_Table(j).column_name := x_View_Column_Table(i);
811 EXIT comment_loop;
812 END IF;
813 END LOOP comment_loop;
814 END LOOP;
815 bis_debug_pub.Add('< make_unique_columns');
816
817
818 EXCEPTION
819 when FND_API.G_EXC_ERROR then
820 x_return_status := FND_API.G_RET_STS_ERROR ;
821 RAISE FND_API.G_EXC_ERROR;
822 when FND_API.G_EXC_UNEXPECTED_ERROR then
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
825 when others then
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
827 BIS_VG_UTIL.Add_Error_Message
828 ( p_error_msg_id => SQLCODE
829 , p_error_description => SQLERRM
830 , p_error_proc_name => G_PKG_NAME||'.make_unique_columns'
831 , p_error_table => x_error_tbl
832 , x_error_table => x_error_tbl
833 );
834 bis_vg_log.update_failure_log( x_error_tbl
835 , x_return_status
836 , x_error_Tbl
837 );
838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
839
840 END make_unique_columns;
841 --
842 -- ============================================================================
843 --PROCEDURE : format_columns
844 --PARAMETERS: 1. p_View_Column_Table table of varchars
845 -- 2. x_View_Column_Table table of varchars with hats removed
846 -- 3. x_return_status error or normal
847 -- 4. x_error_Tbl table of error messages
848 --COMMENT : Call this procedure to ensure column names dont have hats
849 --EXCEPTION : None
850 -- ============================================================================
851 PROCEDURE format_columns -- PRIVATE PROCEDURE
852 ( p_View_Column_Table IN bis_vg_types.View_Text_Table_Type
853 , p_View_Column_Comment_Table IN bis_vg_types.Flex_Column_Comment_Table_Type
854 , x_View_Column_Table IN OUT NOCOPY bis_vg_types.View_Text_Table_Type
855 , x_View_Column_Comment_Table IN OUT NOCOPY bis_vg_types.Flex_Column_Comment_Table_Type
856 , x_return_status OUT NOCOPY VARCHAR2
857 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
858 )
859 IS
860 l_length NUMBER;
861 l_x_View_Column_Table bis_vg_types.View_Text_Table_Type;
862 l_counter NUMBER;
863 l_occurence_counter NUMBER;
864 BEGIN
865 bis_debug_pub.Add('> format_columns');
866 x_return_status := FND_API.G_RET_STS_SUCCESS;
867 FOR i IN 1 .. p_View_Column_Table.COUNT LOOP
868 x_View_Column_Table(i) := REPLACE(p_View_Column_Table(i), '&', '_');
869 x_View_Column_Table(i) := REPLACE(x_View_Column_Table(i), '"','_');
870 x_View_Column_Table(i) := REPLACE(x_View_Column_Table(i), '^', '_');
871 END LOOP;
872 x_View_Column_Comment_Table := p_View_Column_Comment_Table;
873 FOR i IN 1 .. x_View_Column_Comment_Table.COUNT LOOP
874 x_View_Column_Comment_Table(i).column_name := REPLACE(x_View_Column_Comment_Table(i).column_name, '&', '_');
875 x_View_Column_Comment_Table(i).column_name := REPLACE(x_View_Column_Comment_Table(i).column_name, '"', '_');
876 x_View_Column_Comment_Table(i).column_name := REPLACE(x_View_Column_Comment_Table(i).column_name, '^', '_');
877 END LOOP;
878 l_x_View_Column_Table := x_View_Column_Table;
879 -- make sure all column names are unique
880 FOR i IN 1 .. l_x_View_Column_Table.COUNT LOOP
881 FOR j IN i .. x_View_Column_Table.COUNT LOOP
882 IF j <> i AND x_View_Column_Table(j) = l_x_View_Column_Table(i) then
883 l_length := LENGTHB(x_View_Column_Table(j));
884 IF j < 10 THEN
885 x_View_Column_Table(j) := SUBSTRB(x_View_Column_Table(j),1, l_length -1)||j;
886 -- need to keep the changed names synched up with those in the x_View_Column_Comment_Table
887 l_counter := 0;
888 l_occurence_counter := 0;
889 <<comments_loop1>>
890 LOOP
891 l_counter := l_counter + 1;
892 IF x_View_Column_Comment_Table(l_counter).column_name = l_x_View_Column_Table(i) THEN
893 l_occurence_counter := l_occurence_counter +1;
894 END IF;
895 IF l_occurence_counter = 2 THEN
896 x_View_Column_Comment_Table(l_counter).column_name := x_View_Column_Table(j);
897 END IF;
898 EXIT comments_loop1 WHEN l_occurence_counter = 2 OR l_counter = x_View_Column_Comment_Table.COUNT;
899 END LOOP comments_loop1;
900 ELSIF j < 100 THEN
901 x_View_Column_Table(j) := SUBSTRB(x_View_Column_Table(j),1, l_length - 2)||j;
902 -- need to keep the changed names synched up with those in the x_View_Column_Comment_Table
903 l_counter := 0;
904 l_occurence_counter := 0;
905 <<comments_loop2>>
906 LOOP
907 l_counter := l_counter + 1;
908 IF x_View_Column_Comment_Table(l_counter).column_name = l_x_View_Column_Table(i) THEN
909 l_occurence_counter := l_occurence_counter +1;
910 END IF;
911 IF l_occurence_counter = 2 THEN
912 x_View_Column_Comment_Table(l_counter).column_name := x_View_Column_Table(j);
913 END IF;
914 EXIT comments_loop2 WHEN l_occurence_counter = 2 OR l_counter = x_View_Column_Comment_Table.COUNT;
915 END LOOP comments_loop2;
916 ELSE
917 x_View_Column_Table(j) := SUBSTRB(x_View_Column_Table(j),1, l_length - 3)||j;
918 -- need to keep the changed names synched up with those in the x_View_Column_Comment_Table
919 l_counter := 0;
920 l_occurence_counter := 0;
921 <<comments_loop3>>
922 LOOP
923 l_counter := l_counter + 1;
924 IF x_View_Column_Comment_Table(l_counter).column_name = l_x_View_Column_Table(i) THEN
925 l_occurence_counter := l_occurence_counter +1;
926 END IF;
927 IF l_occurence_counter = 2 THEN
928 x_View_Column_Comment_Table(l_counter).column_name := x_View_Column_Table(j);
929 END IF;
930 EXIT comments_loop3 WHEN l_occurence_counter = 2 OR l_counter = x_View_Column_Comment_Table.COUNT;
931 END LOOP comments_loop3;
932 END IF;
933 END IF;
934 END LOOP;
935 END LOOP;
936 -- end column name uniqueness routine
937 -- wrap the column list names in double quotes
938 FOR i IN 1 .. x_View_Column_Table.COUNT LOOP
939 x_View_Column_Table(i) := '"'
940 || x_View_Column_Table(i)
941 || '"';
942 END LOOP;
943 bis_debug_pub.Add('< format_columns');
944
945
946 EXCEPTION
947 when FND_API.G_EXC_ERROR then
948 x_return_status := FND_API.G_RET_STS_ERROR ;
949 RAISE FND_API.G_EXC_ERROR;
950 when FND_API.G_EXC_UNEXPECTED_ERROR then
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
953 when others then
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
955 BIS_VG_UTIL.Add_Error_Message
956 ( p_error_msg_id => SQLCODE
957 , p_error_description => SQLERRM
958 , p_error_proc_name => G_PKG_NAME||'.format_columns'
959 , p_error_table => x_error_tbl
960 , x_error_table => x_error_tbl
961 );
962 bis_vg_log.update_failure_log( x_error_tbl
963 , x_return_status
964 , x_error_Tbl
965 );
966 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
967
968 END format_columns;
969 --
970 -- ============================================================================
971 --PROCEDURE : format_Table
972 --PARAMETERS: 1. p_View_Table_Rec record of view table
973 -- 2. p_View_Column_Table table of varchars
974 -- 3. x_View_Table_Rec updated record of view table
975 -- 4. x_View_Column_Table table of varchars
976 -- 5. x_return_status error or normal
977 -- 6. x_error_Tbl table of error messages
978 --COMMENT : Call this procedure to add the create statement and commas to the
979 -- column table; adds whitespace to end of each line in select table
980 --EXCEPTION : None
981 -- ============================================================================
982 PROCEDURE format_Table -- PRIVATE PROCEDURE
983 ( p_View_name IN VARCHAR2
984 , p_view_column_table IN bis_vg_types.View_Text_Table_Type
985 , p_View_Column_Comment_Table IN bis_vg_types.Flex_Column_Comment_Table_Type
986 , x_View_name OUT NOCOPY VARCHAR2
987 , x_View_Column_Table OUT NOCOPY bis_vg_types.View_Text_Table_Type
988 , x_View_Column_Comment_Table OUT NOCOPY bis_vg_types.Flex_Column_Comment_Table_Type
989
990 ---, x_return_status OUT VARCHAR2
991 , x_error_Tbl OUT NOCOPY BIS_VG_UTIL.Error_Tbl_Type
992 )
993 IS
994 --
995 l_count INTEGER := 1;
996 l_flag BOOLEAN := TRUE;
997
998 l_dummy VARCHAR2(1000);
999 l_pos NUMBER;
1000 l_View_Column_Table bis_vg_types.View_Text_Table_Type;
1001 l_View_Column_Comment_Table bis_vg_types.Flex_Column_Comment_Table_Type;
1002 l_View_Column_Table1 bis_vg_types.View_Text_Table_Type;
1003 l_View_Column_Comment_Table1 bis_vg_types.Flex_Column_Comment_Table_Type;
1004 l_View_Column_Table2 bis_vg_types.View_Text_Table_Type;
1005 l_View_Column_Comment_Table2 bis_vg_types.Flex_Column_Comment_Table_Type;
1006 l_View_Column_Table3 bis_vg_types.View_Text_Table_Type;
1007 l_View_Column_Comment_Table3 bis_vg_types.Flex_Column_Comment_Table_Type;
1008 l_View_Column_Table4 bis_vg_types.View_Text_Table_Type;
1009 l_View_Column_Comment_Table4 bis_vg_types.Flex_Column_Comment_Table_Type;
1010 --
1011 BEGIN
1012 bis_debug_pub.Add('> format_Table');
1013 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
1014 l_pos := INSTRB(p_View_Name, 'V_');
1015 x_View_Name := bis_vg_util.get_generated_view_name
1016 ( p_View_name
1017 , l_dummy
1018 , x_error_Tbl
1019 );
1020
1021 bis_debug_pub.Add('view name is '||x_view_name);
1022 --
1023 l_View_Column_Table := p_View_Column_Table;
1024 l_View_Column_Comment_Table := p_View_Column_Comment_Table;
1025 FOR i IN 1 .. p_View_Column_Table.COUNT LOOP
1026 l_View_Column_Table(i) := REPLACE(l_View_Column_Table(i), ' ', '_');
1027 l_View_Column_Table(i) := REPLACE(l_View_Column_Table(i), '-', '_');
1028 END LOOP;
1029 --
1030 --
1031 FOR i IN 1 .. p_View_Column_Comment_Table.COUNT LOOP
1032 l_View_Column_Comment_Table(i).column_name := REPLACE(l_View_Column_Comment_Table(i).column_name,' ', '_');
1033 l_View_Column_Comment_Table(i).column_name := REPLACE(l_View_Column_Comment_Table(i).column_name,'-', '_');
1034 END LOOP;
1035 --
1036 make_column_len30 ( l_view_column_table
1037 , l_View_Column_Comment_Table
1038 , l_view_column_table1
1039 , l_View_Column_Comment_Table1
1040 , l_dummy
1041 , x_error_Tbl
1042 );
1043
1044 make_unique_columns ( l_view_column_table1
1045 , l_View_Column_Comment_Table1
1046 , l_view_column_table2
1047 , l_View_Column_Comment_Table2
1048 , l_dummy
1049 , x_error_Tbl
1050 );
1051
1052
1053
1054 make_column_len30 ( l_view_column_table2
1055 , l_View_Column_Comment_Table2
1056 , l_view_column_table3
1057 , l_View_Column_Comment_Table3
1058 , l_dummy
1059 , x_error_Tbl
1060 );
1061
1062 format_columns ( l_view_column_table3
1063 , l_View_Column_Comment_Table3
1064 , l_view_column_table4
1065 , l_View_Column_Comment_Table4 -- last manipulation of comment table
1066 , l_dummy
1067 , x_error_Tbl
1068 );
1069
1070 FOR i IN 1 .. l_view_column_comment_table4.COUNT LOOP
1071 x_View_Column_Comment_Table(i).column_name := l_view_column_comment_table4(i).column_name;
1072 x_View_Column_Comment_Table(i).flex_type := l_view_column_comment_table4(i).flex_type;
1073 x_View_Column_Comment_Table(i).column_comments := l_view_column_comment_table4(i).column_comments;
1074 END LOOP;
1075
1076 --
1077 -- column table
1078 x_View_Column_Table(l_count) := 'CREATE OR REPLACE VIEW ' || x_View_Name;
1079 --
1080 FOR l_ind IN 1 .. l_view_column_table4.COUNT LOOP
1081 l_count := l_count + 1;
1082 IF(l_flag = TRUE) then
1083 x_View_Column_Table(l_count) := ' ( ' || l_view_column_table4(l_ind);
1084 l_flag := FALSE;
1085 ELSE
1086 x_View_Column_Table(l_count) := ' , ' || l_view_column_table4(l_ind);
1087 END IF;
1088 END LOOP;
1089
1090 x_View_Column_Table(l_count+1) := ' ) AS ';
1091 --
1092 bis_debug_pub.Add('< format_Table');
1093
1094
1095 EXCEPTION
1096 when FND_API.G_EXC_ERROR then
1097 --- x_return_status := FND_API.G_RET_STS_ERROR ;
1098 RAISE;
1099 when FND_API.G_EXC_UNEXPECTED_ERROR then
1100 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1101 RAISE;
1102 when others then
1103 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1104 BIS_VG_UTIL.Add_Error_Message
1105 ( p_error_msg_id => SQLCODE
1106 , p_error_description => SQLERRM
1107 , p_error_proc_name => G_PKG_NAME||'.format_Table'
1108 , p_error_table => x_error_tbl
1109 , x_error_table => x_error_tbl
1110 );
1111 bis_vg_log.update_failure_log( x_error_tbl
1112 , l_dummy
1113 , x_error_Tbl
1114 );
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116
1117 END format_Table;
1118
1119 --=====================
1120 --Public Procedure
1121 --=====================
1122 --
1123 -- ============================================================================
1124 --PROCEDURE : write_View
1125 --PARAMETERS: 1. p_View_name name of view
1126 -- 2. p_View_Create_Text_Table table of varchars for create view text
1127 -- 3. p_View_Select_Text_Table table of varchars for select view text
1128 -- 4. x_return_status error or normal
1129 -- 5. x_error_Tbl table of error messages
1130 --COMMENT : Call this procedure to create the view given the complete create
1131 -- and select tables.
1132 --EXCEPTION : None
1133 -- ============================================================================
1134 PROCEDURE write_View -- PUBLIC PROCEDURE
1135 ( p_mode IN NUMBER
1136 , p_View_Name IN VARCHAR2
1137 , p_View_Create_Text_Table IN bis_vg_types.View_Text_Table_Type
1138 , p_View_Select_Text_Table IN bis_vg_types.view_text_table_type
1139 , p_View_Column_Comment_Table IN bis_vg_types.Flex_Column_Comment_Table_Type
1140 , x_View_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
1141 ---, x_return_status OUT VARCHAR2
1142 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1143 )
1144 IS
1145 --
1146 l_View_Create_Text_Table bis_vg_types.View_Text_Table_Type;
1147 l_View_name VARCHAR2(100);
1148 l_applsys_schema VARCHAR2(100);
1149 dummy_char VARCHAR2(100);
1150
1151
1152 --
1153 BEGIN
1154
1155 bis_debug_pub.Add('> write_View');
1156 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
1157 format_Table( p_View_name
1158 , p_View_Create_Text_Table
1159 , p_View_Column_Comment_Table
1160 , l_View_name
1161 , l_View_Create_Text_Table
1162 , x_View_Column_Comment_Table
1163 --- , x_return_status
1164 , x_error_Tbl
1165 );
1166
1167
1168 -- Get the schema
1169 IF NOT FND_INSTALLATION.GET_APP_INFO( 'FND'
1170 , dummy_char
1171 , dummy_char
1172 , l_applsys_schema
1173 )
1174 THEN RAISE FND_API.G_EXC_ERROR ;
1175 END IF;
1176
1177
1178 declare
1179 BEGIN
1180 bis_debug_pub.debug_on;
1181 bis_debug_pub.Add('l_applsys_schema = ' || l_applsys_schema);
1182 bis_debug_pub.Add('l_View_Name = '
1183 || l_View_Name);
1184
1185 do_short_ddl(p_mode
1186 , l_View_name
1187 , l_View_Create_Text_Table
1188 , p_View_Select_Text_Table
1189 , l_applsys_schema
1190 , dummy_char
1191 , x_error_Tbl
1192 );
1193 bis_debug_pub.debug_off;
1194 EXCEPTION
1195 WHEN expected_overflow_error
1196 -- We have a view that is too large to fit in a 28000 varchar2
1197 THEN
1198 bis_debug_pub.debug_on;
1199
1200 do_long_ddl( p_mode
1201 , l_View_name
1202 , l_View_Create_Text_Table
1203 , p_View_Select_Text_Table
1204 , l_applsys_schema
1205 , dummy_char
1206 , x_error_Tbl
1207 );
1208 bis_debug_pub.debug_off;
1209 END;
1210
1211 bis_debug_pub.Add('< write_View');
1212
1213 EXCEPTION
1214 when FND_API.G_EXC_ERROR then
1215 --- x_return_status := FND_API.G_RET_STS_ERROR ;
1216 RAISE;
1217 when FND_API.G_EXC_UNEXPECTED_ERROR then
1218 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1219 RAISE;
1220 when others then
1221 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1222 BIS_VG_UTIL.Add_Error_Message
1223 ( p_error_msg_id => SQLCODE
1224 , p_error_description => SQLERRM
1225 , p_error_proc_name => G_PKG_NAME||'.write_View'
1226 , p_error_table => x_error_tbl
1227 , x_error_table => x_error_tbl
1228 );
1229 bis_vg_log.update_failure_log( x_error_tbl
1230 , dummy_char
1231 , x_error_Tbl
1232 );
1233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1234
1235 END write_View;
1236 --
1237 END BIS_VG_COMPILE;