[Home] [Help]
PACKAGE BODY: APPS.BIS_VG_REPOSITORY_MEDIATOR
Source
1 PACKAGE BODY bis_VG_repository_mediator AS
2 /* $Header: BISTRPMB.pls 115.18 2002/08/20 14:36:52 dbowles ship $ */
3
4 -- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --
7 -- FILENAME
8 --
9 -- BISTRPMB.pls
10 --
11 -- DESCRIPTION
12 --
13 -- specification of package which mediates with the repository
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 --
19 -- 29-JUL-98 Created
20 -- 11-DEC-01 Edited by DBOWLES Added dr driver comments.
21 --
22 G_PKG_NAME CONSTANT VARCHAR(30) := 'bis_VG_repository_mediator';
23 --============================================================================
24 -- variables for the String generator
25 --============================================================================
26 g_cursor INTEGER;
27 g_current_posn INTEGER;
28 --
29 -- ============================================================================
30 -- PROCEDURE : String_Generator_Init
31 -- PARAMETERS: 1. p_View_Name View name
32 -- 2. x_return_status error or normal (obsolete)
33 -- 3. x_error_Tbl table of error messages
34 --
35 -- COMMENT : Call this procedure to initialize the string generator
36 -- the runtime repository.
37 -- EXCEPTION : None
38 -- ===========================================================================
39 PROCEDURE string_generator_init
40 ( p_view_name IN BIS_VG_TYPES.view_name_type
41 --- , x_return_status OUT VARCHAR2
42 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
43 )
44 IS
45
46 l_statement VARCHAR2(100)
47 := 'SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = ';
48 l_dummy NUMBER;
49 --
50 BEGIN
51
52 BIS_DEBUG_PUB.Add('> init_string_generator');
53 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
54 l_statement := l_statement || '''' || p_View_Name || '''';
55 g_cursor := DBMS_SQL.OPEN_CURSOR;
56 DBMS_SQL.PARSE(g_cursor, l_statement, DBMS_SQL.NATIVE);
57 DBMS_SQL.DEFINE_COLUMN_LONG(g_cursor, 1);
58 l_dummy := DBMS_SQL.EXECUTE(g_cursor);
59 l_dummy := DBMS_SQL.FETCH_ROWS(g_cursor);
60 g_current_posn := 0;
61 BIS_DEBUG_PUB.Add('< init_string_generator');
62
63
64 EXCEPTION
65 when FND_API.G_EXC_ERROR then
66 --- x_return_status := FND_API.G_RET_STS_ERROR ;
67 RAISE FND_API.G_EXC_ERROR;
68 when FND_API.G_EXC_UNEXPECTED_ERROR then
69 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 when others then
72 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
73 BIS_VG_UTIL.Add_Error_Message
74 ( p_error_msg_id => SQLCODE
75 , p_error_description => SQLERRM
76 , p_error_proc_name => G_PKG_NAME||'.string_generator_init'
77 , p_error_table => x_error_tbl
78 , x_error_table => x_error_tbl
79 );
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81
82 END string_generator_init;
83
84 -- ============================================================================
85 -- PROCEDURE : String_Generator_Get_String
86 -- PARAMETERS: 1. p_chunk_size chunk size to fetch
87 -- 2. x_string return string
88 -- 3. x_eod return true if end of data
89 -- 4. x_return_status error or normal (obsolete)
90 -- 5. x_error_Tbl table of error messages
91 --
92 -- COMMENT : Call this procedure to retrieve a string of given size. It will
93 -- return a string which will end at a delimiter
94 -- EXCEPTION : None
95 -- ===========================================================================
96 PROCEDURE String_Generator_Get_String
97 ( p_chunk_size IN INTEGER
98 , x_string OUT VARCHAR2
99 , x_eod OUT BOOLEAN
100 --- , x_return_status OUT VARCHAR2
101 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
102 )
103 IS
104 l_dummy VARCHAR2(1000);
105 l_chunk_size_ret INTEGER;
106 BEGIN
107 BIS_DEBUG_PUB.Add('> String_Generator_Get_String');
108 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
109 DBMS_SQL.COLUMN_VALUE_LONG( g_cursor
110 , 1
111 , p_chunk_size
112 , g_current_posn
113 , x_string
114 , l_chunk_size_ret
115 );
116
117 x_eod := FALSE;
118 IF (l_chunk_size_ret = p_chunk_size) THEN
119 -- we retrived what was required, check that we end on delimiter
120 WHILE ( NOT bis_vg_util.is_char_delimiter( SUBSTR( x_string
121 , l_chunk_size_ret
122 , 1
123 )
124 , l_dummy
125 , x_error_Tbl
126 )
127 ) LOOP
128 l_chunk_size_ret := l_chunk_size_ret - 1;
129 END LOOP;
130 g_current_posn := g_current_posn + l_chunk_size_ret;
131 x_string := Substr(x_string, 1, l_chunk_size_ret);
132 ELSE
133 x_eod := TRUE;
134 END IF;
135 BIS_DEBUG_PUB.Add('< String_Generator_Get_String');
136
137
138 EXCEPTION
139 when FND_API.G_EXC_ERROR then
140 --- x_return_status := FND_API.G_RET_STS_ERROR ;
141 RAISE FND_API.G_EXC_ERROR;
142 when FND_API.G_EXC_UNEXPECTED_ERROR then
143 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
145 when others then
146 --- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
147 BIS_VG_UTIL.Add_Error_Message
148 ( p_error_msg_id => SQLCODE
149 , p_error_description => SQLERRM
150 , p_error_proc_name => G_PKG_NAME||'.String_Generator_Get_String'
151 , p_error_table => x_error_tbl
152 , x_error_table => x_error_tbl
153 );
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155
156 END String_Generator_Get_String;
157
158
159 -- ============================================================================
160 -- PROCEDURE : create_View_Select_Text_Table
161 -- PARAMETERS:
162 -- 1. p_View_name view name
163 -- 2. x_View_Select_Text_Table table of varchars to hold select
164 -- 3. x_error_Tbl table of error messages
165 -- view text
166 -- COMMENT : Call this procedure to retrieve select clause of the view from
167 -- the runtime repository.
168 -- EXCEPTION : None
169 -- ============================================================================
170 PROCEDURE create_View_Select_Text_Table
171 ( p_view_name IN BIS_VG_TYPES.View_name_Type := null
172 , x_View_Select_Text_Table OUT BIS_VG_TYPES.View_Text_Table_Type
173 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
174 )
175 IS
176 --
177 l_eod BOOLEAN;
178 l_string VARCHAR2(200);
179
180 BEGIN
181
182 BIS_DEBUG_PUB.Add('> create_View_Select_Text_Table');
183 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 l_eod := FALSE;
186
187 string_generator_init ( p_view_name
188 --- , x_return_status
189 , x_error_Tbl
190 );
191 WHILE (NOT l_eod) LOOP
192 string_generator_get_string( 200
193 , l_string
194 , l_eod
195 --- , x_return_status
196 , x_error_Tbl
197 );
198
199 bis_debug_pub.add('l_string := ' || l_string);
200 x_View_Select_Text_Table(x_View_Select_Text_Table.COUNT + 1):= l_string;
201
202 END LOOP;
203
204 DBMS_SQL.CLOSE_CURSOR(g_cursor);
205
206 BIS_DEBUG_PUB.Add('< create_View_Select_Text_Table');
207
208
209 EXCEPTION
210 when FND_API.G_EXC_ERROR then
211 DBMS_SQL.CLOSE_CURSOR(g_cursor);
212
213 RAISE FND_API.G_EXC_ERROR;
214 when FND_API.G_EXC_UNEXPECTED_ERROR then
215 DBMS_SQL.CLOSE_CURSOR(g_cursor);
216
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 when others then
219 DBMS_SQL.CLOSE_CURSOR(g_cursor);
220
221 BIS_VG_UTIL.Add_Error_Message
222 ( p_error_msg_id => SQLCODE
223 , p_error_description => SQLERRM
224 , p_error_proc_name => G_PKG_NAME||'.create_View_Select_Text_Table'
225 , p_error_table => x_error_tbl
226 , x_error_table => x_error_tbl
227 );
228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229
230 END create_View_Select_Text_Table;
231 ---
232 --- ============================================================================
233 --- PROCEDURE : create_View_Text_Tables
234 --- PARAMETERS: 1. p_View_name view name
235 --- 2. x_View_Create_Text_Table table of varchars to hold create
236 --- view text
237 --- 3. x_View_Select_Text_Table table of varchars to hold select
238 --- view
239 --- 4. x_error_Tbl table of error messages
240 --- text
241 --- COMMENT : Call this procedure to retrieve the view text from the runtime
242 --- repository.
243 --- EXCEPTION : None
244 --- ============================================================================
245 PROCEDURE create_View_Text_Tables
246 ( p_view_name IN BIS_VG_TYPES.View_name_type := null
247 , x_View_Column_Text_Table OUT BIS_VG_TYPES.View_Text_Table_Type
248 , x_View_Select_Text_Table OUT BIS_VG_TYPES.View_Text_Table_Type
249 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
250 )
251 IS
252 l_count NUMBER;
253 l_done BOOLEAN;
254 l_text_count NUMBER;
255 l_start NUMBER;
256 l_ViewText LONG;
257 l_str VARCHAR2(255);
258 l_pos NUMBER;
259 l_char VARCHAR2(1);
260 --
261 CURSOR c_all_columns IS
262 select COLUMN_NAME
263 from user_tab_columns
264 where TABLE_NAME=Upper(p_view_name)
265 order by COLUMN_ID;
266 --
267 BEGIN
268 BIS_DEBUG_PUB.Add('> create_View_Text_Tables');
269 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
270 --
271 l_Done := FALSE;
272 l_count := 1;
273 --
274 -- get the select text
275 Bis_debug_pub.Add('view name = '||p_view_name);
276
277 create_View_Select_Text_Table ( p_View_name
278 , x_View_Select_Text_Table
279 --- , x_return_status
280 , x_error_Tbl
281 );
282 --
283 BIS_DEBUG_PUB.Add('text count = '||x_View_Select_Text_Table.COUNT);
284 l_count := 1;
285 -- get the columns
286 FOR cr IN c_all_columns LOOP
287 x_view_column_text_table(l_count) := cr.column_name;
288 l_count := l_count + 1;
289 END LOOP;
290 --
291 BIS_DEBUG_PUB.Add('< create_View_Text_Tables');
292
293 EXCEPTION
294 when FND_API.G_EXC_ERROR then
295
296 CLOSE c_all_columns;
297 RAISE FND_API.G_EXC_ERROR;
298 when FND_API.G_EXC_UNEXPECTED_ERROR then
299
300 CLOSE c_all_columns;
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 when others then
303
304 CLOSE c_all_columns;
305 BIS_VG_UTIL.Add_Error_Message
306 ( p_error_msg_id => SQLCODE
307 , p_error_description => SQLERRM
308 , p_error_proc_name => G_PKG_NAME||'.create_View_Text_Tables'
309 , p_error_table => x_error_tbl
310 , x_error_table => x_error_tbl
311 );
312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
313
314 END create_View_Text_Tables;
315
316
317 --
318 /* ============================================================================
319 FUNCTION Get_App_Info
320 PARAMETERS : 1. p_view_rec IN view name
321 2. x_return_status error or normal
322 3. x_error_Tbl table of error messages
323 Comment : fills in the app_id, short_name for business views
324 returns view record with all the info
325 Exception : none
326 ========================================================================== */
327 FUNCTION get_app_info
328 ( p_view_rec IN bis_vg_types.view_table_rec_type
329 , x_return_status OUT VARCHAR2
330 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
331 )
332 RETURN bis_vg_types.view_table_rec_type
333 IS
334 l_str fnd_application.application_short_name%TYPE;
335 l_pos NUMBER;
336 l_view_rec bis_vg_types.view_table_rec_type;
337
338 CURSOR app_cursor(p_short_name IN VARCHAR2) IS
342
339 SELECT application_id
340 FROM fnd_application
341 WHERE application_short_name = Lower(p_short_name);
343 BEGIN
344 BIS_DEBUG_PUB.Add('> get_app_info');
345 x_return_status := FND_API.G_RET_STS_SUCCESS;
346 l_view_rec := p_view_rec;
347 l_pos := Instr(l_view_rec.view_name, 'BV_');
348 IF (l_pos = 0) THEN
349 l_pos := Instr(l_view_rec.view_name, 'FV_');
350 IF (l_pos = 0) THEN
351 BIS_DEBUG_PUB.Add('< get_app_info');
352 RETURN NULL;
353 END IF;
354 END IF;
355
356 l_view_rec.app_short_name := Substr(l_view_rec.view_name, 1, l_pos - 1);
357
358 IF ( l_view_rec.app_short_name = 'GL'
359 OR l_view_rec.app_short_name = 'AP') THEN
360 -- GL and AP are special cases
361 l_view_rec.app_short_name := 'SQL'||l_view_rec.app_short_name;
362 END IF;
363
364 FOR cr IN app_cursor(l_view_rec.app_short_name) LOOP
365 l_view_rec.application_id := cr.application_id;
366 END LOOP;
367
368 BIS_DEBUG_PUB.Add('< get_app_info');
369 RETURN l_view_rec;
370 CLOSE app_cursor;
371
372 EXCEPTION
373 when FND_API.G_EXC_ERROR then
374 x_return_status := FND_API.G_RET_STS_ERROR ;
375 CLOSE app_cursor;
376 RAISE FND_API.G_EXC_ERROR;
377 when FND_API.G_EXC_UNEXPECTED_ERROR then
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
379 CLOSE app_cursor;
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 when others then
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
383 CLOSE app_cursor;
384 BIS_VG_UTIL.Add_Error_Message
385 ( p_error_msg_id => SQLCODE
386 , p_error_description => SQLERRM
387 , p_error_proc_name => G_PKG_NAME||'.get_app_info'
388 , p_error_table => x_error_tbl
389 , x_error_table => x_error_tbl
390 );
391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
392
393 END get_app_info;
394
395 /* ============================================================================
396 FUNCTION : valid_view
397 PARAMETERS: 1. p_comapre_string compare string for the field
398 2. p_view_name name of the view
399 3. x_return_status error or normal
400 4. x_error_Tbl table of error messages
401 RETURNS BOOLEAN
402 COMMENT : returns true is view text contains given compare string
403
404 EXCEPTION : None
405 ========================================================================== */
406 FUNCTION valid_view
407 ( p_compare_string IN VARCHAR2
408 , p_view_name IN VARCHAR2
409 , x_return_status OUT VARCHAR2
410 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
411 )
412 RETURN BOOLEAN
413 IS
414 l_eod BOOLEAN;
415 l_found BOOLEAN := FALSE;
416 l_string VARCHAR2(32000);
417 l_compare_string_u VARCHAR2(1000);
418 l_compare_string_l VARCHAR2(1000);
419 BEGIN
420
421 BIS_DEBUG_PUB.Add('> valid_view');
422 l_eod := FALSE;
423 l_compare_string_u := Upper(p_compare_string);
424 l_compare_string_l := Lower(p_compare_string);
425
426 string_generator_init ( p_view_name
427 --- , x_return_status
428 , x_error_Tbl
429 );
430 WHILE (NOT l_eod AND NOT l_found) LOOP
431 string_generator_get_string( 32000
432 , l_string
433 , l_eod
434 --- , x_return_status
435 , x_error_Tbl
436 );
437
438 IF ((Instr(l_string, l_compare_string_l) <> 0) OR
439 (Instr(l_string, l_compare_string_u) <> 0)) THEN
440 l_found := TRUE;
441 END IF;
442
443 END LOOP;
444
445 DBMS_SQL.CLOSE_CURSOR(g_cursor);
446
447 BIS_DEBUG_PUB.Add('< valid_view');
448
449 RETURN l_found;
450
451
452 EXCEPTION
453 when FND_API.G_EXC_ERROR then
454 x_return_status := FND_API.G_RET_STS_ERROR ;
455 dbms_sql.close_cursor(g_cursor);
456 RAISE FND_API.G_EXC_ERROR;
457 when FND_API.G_EXC_UNEXPECTED_ERROR then
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
459 dbms_sql.close_cursor(g_cursor);
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 when others then
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
463 dbms_sql.close_cursor(g_cursor);
464 BIS_VG_UTIL.Add_Error_Message
465 ( p_error_msg_id => SQLCODE
466 , p_error_description => SQLERRM
467 , p_error_proc_name => G_PKG_NAME||'.valid_view'
468 , p_error_table => x_error_tbl
469 , x_error_table => x_error_tbl
470 );
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472
473 END valid_view;
474
475 /* ============================================================================
476 PROCEDURE : retrieve_business_views_field
477 PARAMETERS: 1. p_comapre_string compare string for the field
478 2. p_search_string string to limit the views
479 3. x_View_Table returned list of views
483
480 4. x_return_status error or normal
481 5. x_error_Tbl table of error messages
482 COMMENT : Call this procedure get all the view with a particular field
484 EXCEPTION : None
485 ========================================================================== */
486 PROCEDURE retrieve_business_views_field
487 ( p_compare_string IN VARCHAR2
488 , p_search_string IN VARCHAR2
489 , x_View_Table OUT BIS_VG_TYPES.view_table_type
490 , x_return_status OUT VARCHAR2
491 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
492 )
493 IS
494
495 CURSOR c_field_views(p_like_str IN VARCHAR2) IS
496 select
497 view_name
498 , text_length
499 FROM user_views
500 WHERE
501 (
502 view_name LIKE '__BV\_%' escape '\'
503 OR view_name LIKE '__FV\_%' escape '\'
504 OR view_name LIKE '___BV\_%' escape '\'
505 OR view_name LIKE '___FV\_%' escape '\'
506 )
507 AND view_name IN (
508 SELECT
509 DISTINCT(table_name) view_name
510 FROM user_tab_columns
511 WHERE
512 column_name LIKE Upper(p_like_str) escape '\'
513 OR column_name LIKE Lower(p_like_str) escape '\'
514 )
515 ;
516
517 l_view_rec bis_vg_types.view_table_rec_type;
518 l_valid_view BOOLEAN;
519 begin
520 BIS_DEBUG_PUB.Add('> retrieve_business_views_field');
521
522 FOR cr IN c_field_views(p_search_string) LOOP
523 l_view_rec.view_name := cr.view_name;
524 l_view_rec.text_length := cr.text_length;
525 l_view_rec := get_app_info ( l_view_rec
526 , x_return_status
527 , x_error_Tbl
528 );
529 l_valid_view := valid_view ( p_compare_string
530 , l_view_rec.view_name
531 , x_return_status
532 , x_error_Tbl
533 );
534 IF (l_valid_view = TRUE) THEN
535 x_view_table(x_view_table.COUNT + 1) := l_view_rec;
536 END IF;
537
538 END LOOP;
539 BIS_DEBUG_PUB.Add('< retrieve_business_views_field');
540
541 EXCEPTION
542 when FND_API.G_EXC_ERROR then
543 x_return_status := FND_API.G_RET_STS_ERROR ;
544 CLOSE c_field_views;
545 RAISE FND_API.G_EXC_ERROR;
546 when FND_API.G_EXC_UNEXPECTED_ERROR then
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
548 CLOSE c_field_views;
549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
550 when others then
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
552 CLOSE c_field_views;
553 BIS_VG_UTIL.Add_Error_Message
554 ( p_error_msg_id => SQLCODE
555 , p_error_description => SQLERRM
556 , p_error_proc_name => G_PKG_NAME||'.retrieve_business_views_field'
557 , p_error_table => x_error_tbl
558 , x_error_table => x_error_tbl
559 );
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
561
562 END retrieve_business_views_field;
563
564 /* ============================================================================
565 PROCEDURE : retrieve_business_views_kfx
566 PARAMETERS: 1. p_KF_Appl_Short_Name application short name
567 2. p_Key_Flex_Code key flexfield code
568 3. x_View_Table returned list of views 3.
569 4. x_return_status error or normal
570 5. x_error_Tbl table of error messages
571 COMMENT : Call this procedure get all the view with a particular kfx
572
573 EXCEPTION : None
574 ========================================================================== */
575 PROCEDURE retrieve_business_views_kfx
576 ( p_KF_App_Short_Name IN BIS_VG_TYPES.App_Short_Name_Type := NULL
577 , p_Key_Flex_Code IN BIS_VG_TYPES.Key_Flex_Code_Type := NULL
578 , x_View_Table OUT BIS_VG_TYPES.view_table_type
579 , x_return_status OUT VARCHAR2
580 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
581 )
582 IS
583
584 l_compare_string VARCHAR2(100);
585 l_search_string VARCHAR2(100);
586
587 BEGIN
588
589 BIS_DEBUG_PUB.Add('> retrieve_business_views_kfx');
590
591 l_compare_string := '_KF:' || p_kf_app_short_name
592 ||':' ||p_key_flex_code;
593
594 l_search_string := '\_KF:%';
595
596 retrieve_business_views_field( l_compare_string
597 , l_search_string
598 , x_view_table
599 , x_return_status
600 , x_error_Tbl
601 );
602 BIS_DEBUG_PUB.Add('< retrieve_business_views_kfx');
603
604 EXCEPTION
605 when FND_API.G_EXC_ERROR then
606 x_return_status := FND_API.G_RET_STS_ERROR ;
607 RAISE FND_API.G_EXC_ERROR;
608 when FND_API.G_EXC_UNEXPECTED_ERROR then
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 when others then
615 , p_error_description => SQLERRM
612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
613 BIS_VG_UTIL.Add_Error_Message
614 ( p_error_msg_id => SQLCODE
616 , p_error_proc_name => G_PKG_NAME||'.retrieve_business_views_kfx'
617 , p_error_table => x_error_tbl
618 , x_error_table => x_error_tbl
619 );
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621
622 END retrieve_business_views_kfx;
623
624 /* ============================================================================
625 PROCEDURE : retrieve_business_views_dfx
626 PARAMETERS: 1. p_DF_Appl_Short_Name application short name
627 2. p_Desc_Flex_Name descriptive flexfield name
628 3. x_View_Table returned list of views 3.
629 4. x_return_status error or normal
630 5. x_error_Tbl table of error messages
631 COMMENT : Call this procedure get all the view with a particular dfx
632
633 EXCEPTION : None
634 ========================================================================== */
635 PROCEDURE retrieve_business_views_dfx
636 ( p_DF_App_Short_Name IN BIS_VG_TYPES.App_Short_Name_Type := NULL
637 , p_Desc_Flex_Name IN BIS_VG_TYPES.Desc_Flex_Name_Type := NULL
638 , x_View_Table OUT BIS_VG_TYPES.view_table_type
639 , x_return_status OUT VARCHAR2
640 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
641 )
642 IS
643
644 l_compare_string VARCHAR2(100);
645 l_search_string VARCHAR2(100);
646
647 BEGIN
648
649 BIS_DEBUG_PUB.Add('> retrieve_business_views_dfx');
650
651 l_compare_string := '_DF:' || p_df_app_short_name
652 ||':' ||p_Desc_Flex_Name;
653
654 l_search_string := '\_DF%';
655
656 retrieve_business_views_field( l_compare_string
657 , l_search_string
658 , x_view_table
659 , x_return_status
660 , x_error_Tbl
661 );
662 BIS_DEBUG_PUB.Add('< retrieve_business_views_dfx');
663
664 EXCEPTION
665 when FND_API.G_EXC_ERROR then
666 x_return_status := FND_API.G_RET_STS_ERROR ;
667 RAISE FND_API.G_EXC_ERROR;
668 when FND_API.G_EXC_UNEXPECTED_ERROR then
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
671 when others then
672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
673 BIS_VG_UTIL.Add_Error_Message
674 ( p_error_msg_id => SQLCODE
675 , p_error_description => SQLERRM
676 , p_error_proc_name => G_PKG_NAME||'.retrieve_business_views_dfx'
677 , p_error_table => x_error_tbl
678 , x_error_table => x_error_tbl
679 );
680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
681
682 END retrieve_business_views_dfx;
683
684 /* ============================================================================
685 PROCEDURE : retrieve_business_views_lat
686 PARAMETERS:
687 1. p_Lookup_Table_Name lookup table name
688 2. p_Lookup_Type lookup code
689 3. x_View_Table returned list of views 3.
690 4. x_return_status error or normal
691 5. x_error_Tbl table of error messages
692
693 COMMENT : Call this procedure get all the view with a particular lat
694
695 EXCEPTION : None
696 ========================================================================== */
697 PROCEDURE retrieve_business_views_lat
698 ( p_Lookup_Table_Name IN VARCHAR2
699 , p_Lookup_Type IN BIS_VG_TYPES.Lookup_Code_Type
700 , x_View_Table OUT BIS_VG_TYPES.view_table_type
701 , x_return_status OUT VARCHAR2
702 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
703 )
704 IS
705
706 l_compare_string VARCHAR2(100);
707 l_search_string VARCHAR2(100);
708
709 BEGIN
710
711 BIS_DEBUG_PUB.Add('> retrieve_business_views_lat');
712
713 l_compare_string := ':'||p_lookup_table_name||':'||p_lookup_Type||':';
714
715 l_search_string := '\_LA:%';
716
717 retrieve_business_views_field( l_compare_string
718 , l_search_string
719 , x_view_table
720 , x_return_status
721 , x_error_Tbl
722 );
723 BIS_DEBUG_PUB.Add('< retrieve_business_views_lat');
724
725 EXCEPTION
726 when FND_API.G_EXC_ERROR then
727 x_return_status := FND_API.G_RET_STS_ERROR ;
728 RAISE FND_API.G_EXC_ERROR;
729 when FND_API.G_EXC_UNEXPECTED_ERROR then
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 when others then
733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
734 BIS_VG_UTIL.Add_Error_Message
735 ( p_error_msg_id => SQLCODE
736 , p_error_description => SQLERRM
737 , p_error_proc_name => G_PKG_NAME||'.retrieve_business_views_lat'
738 , p_error_table => x_error_tbl
739 , x_error_table => x_error_tbl
743 END retrieve_business_views_lat;
740 );
741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
742
744
745 /* ============================================================================
746 PROCEDURE : retrieve_Business_View_name
747 PARAMETERS:
748 1. p_view_name name of the view
749 2. x_View_Table returned list of views
750 3. x_return_status error or normal
751 4. x_error_Tbl table of error messages
752
753 COMMENT : Call this procedure to retrieve the business views
754 from the runtime repository.
755 EXCEPTION : None
756 ========================================================================== */
757 PROCEDURE retrieve_business_view_name
758 ( p_view_name IN BIS_VG_TYPES.View_name_Type
759 , x_View_Table OUT BIS_VG_TYPES.view_table_type
760 , x_return_status OUT VARCHAR2
761 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
762 )
763 IS
764 CURSOR C_all_views IS
765 select
766 view_name
767 , text_length
768 FROM user_views
769 WHERE view_name = Upper(p_view_name);
770 l_view_rec bis_vg_types.view_table_rec_type;
771 BEGIN
772 BIS_DEBUG_PUB.Add('> retrieve_Business_Views_view_name');
773 BIS_DEBUG_PUB.Add('view-name is '||p_view_name);
774 FOR cr IN c_all_views LOOP
775 l_view_rec.view_name := cr.view_name;
776 l_view_rec.text_length := cr.text_length;
777 l_view_rec := get_app_info(l_view_rec, x_return_status, x_error_Tbl);
778 IF l_view_rec.view_name IS NOT NULL THEN
779 x_view_table(x_view_table.COUNT + 1) := l_view_rec;
780 END IF;
781
782 END LOOP;
783 BIS_DEBUG_PUB.Add('< retrieve_Business_Views_view_name');
784
785 EXCEPTION
786 when FND_API.G_EXC_ERROR then
787 x_return_status := FND_API.G_RET_STS_ERROR ;
788 CLOSE c_all_views;
789 RAISE FND_API.G_EXC_ERROR;
790 when FND_API.G_EXC_UNEXPECTED_ERROR then
791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
792 CLOSE c_all_views;
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 when others then
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
796 CLOSE c_all_views;
797 BIS_VG_UTIL.Add_Error_Message
798 ( p_error_msg_id => SQLCODE
799 , p_error_description => SQLERRM
800 , p_error_proc_name => G_PKG_NAME||'.retrieve_Business_View_name'
801 , p_error_table => x_error_tbl
802 , x_error_table => x_error_tbl
803 );
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805
806 END retrieve_Business_View_name;
807
808 /* ============================================================================
809 PROCEDURE : retrieve_Business_Views_app
810 PARAMETERS:
811 1. p_view_name name of the view
812 2. x_View_Table returned list of views
813 3. x_return_status error or normal
814 4. x_error_Tbl table of error messages
815
816 COMMENT : Call this procedure to retrieve the business views
817 from the runtime repository.
818 EXCEPTION : None
819 ========================================================================== */
820 PROCEDURE retrieve_business_views_app
821 ( p_app_short_name IN BIS_VG_TYPES.App_Short_Name_Type
822 , x_View_Table OUT BIS_VG_TYPES.view_table_type
823 , x_return_status OUT VARCHAR2
824 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
825 )
826 IS
827
828 CURSOR c_all_views(p_app_abbrev IN VARCHAR2) IS
829 select
830 view_name
831 , text_length
832 FROM user_views
833 WHERE view_name LIKE Upper(p_app_abbrev)||'BV\_%' escape '\'
834 OR view_name LIKE Upper(p_app_abbrev)||'FV\_%' escape '\';
835 -- Handle PER(HR) product views as a special case
836 CURSOR c_hr_views is
837 select
838 view_name
839 , text_length
840 FROM user_views
841 WHERE view_name LIKE 'HR'||'BV\_%' escape '\'
842 OR view_name LIKE 'HR'||'FV\_%' escape '\'
843 OR view_name LIKE 'IRC'||'BV\_%' escape '\'
844 OR view_name LIKE 'IRC'||'FV\_%' escape '\';
845
846 l_count NUMBER := 1;
847 l_view_rec bis_vg_types.view_table_rec_type;
848 l_app_abbrev BIS_VG_TYPES.app_short_name_type;
849 l_view_table BIS_VG_TYPES.view_table_type;
850 BEGIN
851
852 BIS_DEBUG_PUB.Add('> retrieve_Business_Views_app');
853
854 BIS_DEBUG_PUB.Add('short_name = '||p_app_short_name);
855 -- Handle HR product views as a special case
856 IF UPPER(p_app_short_name) = 'HR' then
857 FOR cr IN c_hr_views
858 LOOP
859 BIS_DEBUG_PUB.Add('view_name = '||cr.view_name);
860 BIS_DEBUG_PUB.Add('text length = '||cr.text_length);
861 l_view_rec.view_name := cr.view_name;
862 l_view_rec.text_length := cr.text_length;
863 l_view_rec := get_app_info(l_view_rec, x_return_status, x_error_Tbl);
864 x_view_table(l_count) := l_view_rec;
865 l_count := l_count +1;
866 BIS_DEBUG_PUB.Add('l_count = '||l_count);
867 END LOOP;
868 ELSE
869 FOR cr IN c_all_views(p_app_short_name)
870 LOOP
874 l_view_rec.text_length := cr.text_length;
871 BIS_DEBUG_PUB.Add('view_name = '||cr.view_name);
872 BIS_DEBUG_PUB.Add('text length = '||cr.text_length);
873 l_view_rec.view_name := cr.view_name;
875 l_view_rec := get_app_info(l_view_rec, x_return_status, x_error_Tbl);
876 x_view_table(l_count) := l_view_rec;
877 l_count := l_count +1;
878 BIS_DEBUG_PUB.Add('l_count = '||l_count);
879 END LOOP;
880 END IF;
881
882 -- handle special cases
883 l_app_abbrev := Upper(p_app_short_name);
884
885 IF(Substr(l_app_abbrev, 1, 3) = 'SQL') THEN
886 l_app_abbrev := Substr(l_app_abbrev, 4);
887 retrieve_business_views_app ( l_app_abbrev
888 , l_view_table
889 , x_return_status
890 , x_error_Tbl
891 );
892 ELSE
893 IF (l_app_abbrev='OE') THEN
894 retrieve_business_views_app('WSH'
895 , l_view_table
896 , x_return_status
897 , x_error_Tbl
898 );
899 ELSE
900 IF (l_app_abbrev='PER') THEN
901 retrieve_business_views_app('HR'
902 , l_view_table
903 , x_return_status
904 , x_error_Tbl
905 );
906
907 ELSE
908 IF (l_app_abbrev='OTA') THEN
909 retrieve_business_views_app('OT'
910 , l_view_table
911 , x_return_status
912 , x_error_Tbl
913 );
914 ELSE
915 IF (l_app_abbrev='OFA') THEN
916 retrieve_business_views_app('FA'
917 , l_view_table
918 , x_return_status
919 , x_error_Tbl
920 );
921 END IF;
922 END IF;
923 END IF;
924 END IF;
925 END IF;
926
927 FOR i IN 1 .. l_view_table.COUNT LOOP
928 x_view_table(x_view_table.COUNT + 1) := l_view_table(i);
929 END LOOP;
930
931 BIS_DEBUG_PUB.Add('< retrieve_Business_Views_app');
932
933
934 EXCEPTION
935 when FND_API.G_EXC_ERROR then
936 x_return_status := FND_API.G_RET_STS_ERROR ;
937 CLOSE c_all_views;
938 RAISE FND_API.G_EXC_ERROR;
939 when FND_API.G_EXC_UNEXPECTED_ERROR then
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
941 CLOSE c_all_views;
942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
943 when others then
944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
945 CLOSE c_all_views;
946 BIS_VG_UTIL.Add_Error_Message
947 ( p_error_msg_id => SQLCODE
948 , p_error_description => SQLERRM
949 , p_error_proc_name => G_PKG_NAME||'.retrieve_business_views_app'
950 , p_error_table => x_error_tbl
951 , x_error_table => x_error_tbl
952 );
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954
955 END retrieve_business_views_app;
956
957 /* ============================================================================
958 PROCEDURE : retrieve_Business_Views_all
959 PARAMETERS:
960 1. x_View_Table returned list of views
961 2. x_return_status error or normal
962 3. x_error_Tbl table of error message
963
964 COMMENT : Call this procedure to retrieve all the business views
965
966 EXCEPTION : None
967 ========================================================================== */
968 PROCEDURE retrieve_business_views_all
969 (x_View_Table OUT BIS_VG_TYPES.view_table_type
970 , x_return_status OUT VARCHAR2
971 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
972 )
973 IS
974
975 CURSOR C_all_views IS
976 select
977 view_name
978 , text_length
979 FROM user_views
980 WHERE view_name LIKE '__BV\_%' escape '\'
981 OR view_name LIKE '__FV\_%' escape '\'
982 OR view_name LIKE '___BV\_%' escape '\'
983 OR view_name LIKE '___FV\_%' escape '\';
984
985 l_view_rec bis_vg_types.view_table_rec_type;
986
987 BEGIN
988
989 FOR cr IN c_all_views LOOP
990 l_view_rec.view_name := cr.view_name;
991 l_view_rec.text_length := cr.text_length;
992 l_view_rec := get_app_info ( l_view_rec
993 , x_return_status
994 , x_error_Tbl
995 );
996
997 x_view_table(x_view_table.COUNT + 1) := l_view_rec;
998 END LOOP;
999
1000
1001 EXCEPTION
1002 when FND_API.G_EXC_ERROR then
1003 x_return_status := FND_API.G_RET_STS_ERROR ;
1004 CLOSE c_all_views;
1005 RAISE FND_API.G_EXC_ERROR;
1006 when FND_API.G_EXC_UNEXPECTED_ERROR then
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1008 CLOSE c_all_views;
1009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1010 when others then
1011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1012 CLOSE c_all_views;
1013 BIS_VG_UTIL.Add_Error_Message
1014 ( p_error_msg_id => SQLCODE
1015 , p_error_description => SQLERRM
1016 , p_error_proc_name => G_PKG_NAME||'.retrieve_business_views_all'
1017 , p_error_table => x_error_tbl
1021
1018 , x_error_table => x_error_tbl
1019 );
1020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1022 END retrieve_business_views_all;
1023
1024 /* ============================================================================
1025 PROCEDURE : retrieve_Business_Views
1026 PARAMETERS: 1. p_all_flag retrieve all views for all products
1027 2. p_App_Short_Name application short name
1028 3. p_KF_Appl_Short_Name application short name
1029 4. p_Key_Flex_Code key flexfield code
1030 5. p_DF_Appl_Short_Name application short name
1031 6. p_Desc_Flex_Name descriptive flexfield name
1032 7. p_Lookup_Table_Name lookup table name
1033 8. p_Lookup_Code lookup code
1034 9. p_View_Name name of view to generate
1035 10. x_View_Table table to hold view definitions
1036 11. x_return_status error or normal
1037 12. x_error_Tbl table of error messages
1038
1039 COMMENT : Call this procedure to retrieve the business views from the
1040 runtime repository.
1041 EXCEPTION : None
1042 ========================================================================== */
1043 PROCEDURE retrieve_Business_Views
1044 ( p_all_flag IN VARCHAR2 := NULL
1045 , p_App_Short_name IN BIS_VG_TYPES.App_Short_Name_Type := NULL
1046 , p_KF_Appl_Short_Name IN BIS_VG_TYPES.App_Short_Name_Type := NULL
1047 , p_Key_Flex_Code IN BIS_VG_TYPES.Key_Flex_Code_Type := NULL
1048 , p_DF_Appl_Short_Name IN BIS_VG_TYPES.App_Short_Name_Type := NULL
1049 , p_Desc_Flex_Name IN BIS_VG_TYPES.Desc_Flex_Name_Type := NULL
1050 , p_Lookup_Table_Name IN VARCHAR2 := NULL
1051 , p_Lookup_Type IN BIS_VG_TYPES.Lookup_Code_Type := NULL
1052 , p_View_Name IN BIS_VG_TYPES.View_name_Type := NULL
1053 , x_View_Table OUT BIS_VG_TYPES.View_Table_Type
1054 , x_return_status OUT VARCHAR2
1055 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1056 )
1057 IS
1058 --
1059 BEGIN
1060
1061 BIS_DEBUG_PUB.Add('> retrieve_Business_Views');
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063
1064 IF (p_all_flag = fnd_api.g_true) THEN
1065
1066 retrieve_business_views_all ( x_view_table
1067 , x_return_status
1068 , x_error_Tbl
1069 );
1070
1071 ELSIF (p_view_name IS NOT NULL) THEN
1072
1073 retrieve_business_view_name( p_view_name
1074 , x_view_table
1075 , x_return_status
1076 , x_error_Tbl
1077 );
1078
1079 ELSIF (p_app_short_name IS NOT NULL) THEN
1080
1081 retrieve_business_views_app(p_app_short_name
1082 , x_view_table
1083 , x_return_status
1084 , x_error_Tbl
1085 );
1086
1087
1088 ELSIF( p_kf_appl_short_name IS NOT NULL
1089 AND p_key_flex_code IS NOT NULL
1090 ) THEN
1091
1092 retrieve_business_views_kfx( p_kf_appl_short_name
1093 , p_key_flex_code
1094 , x_view_table
1095 , x_return_status
1096 , x_error_Tbl
1097 );
1098
1099 ELSIF( p_DF_Appl_Short_Name IS NOT NULL
1100 AND p_Desc_Flex_Name IS NOT NULL
1101 ) THEN
1102
1103 retrieve_business_views_dfx( p_df_appl_short_name
1104 , p_desc_flex_name
1105 , x_view_table
1106 , x_return_status
1107 , x_error_Tbl
1108 );
1109
1110 ELSE
1111
1112 retrieve_business_views_lat( p_lookup_table_name
1113 , p_lookup_Type
1114 , x_view_table
1115 , x_return_status
1116 , x_error_Tbl
1117 );
1118
1119 END IF;
1120
1121 BIS_DEBUG_PUB.Add('< retrieve_Business_Views');
1122
1123 EXCEPTION
1124 when FND_API.G_EXC_ERROR then
1125 x_return_status := FND_API.G_RET_STS_ERROR ;
1126 RAISE FND_API.G_EXC_ERROR;
1127 when FND_API.G_EXC_UNEXPECTED_ERROR then
1128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1130 when others then
1131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1132 BIS_VG_UTIL.Add_Error_Message
1133 ( p_error_msg_id => SQLCODE
1134 , p_error_description => SQLERRM
1135 , p_error_proc_name => G_PKG_NAME||'.retrieve_Business_Views'
1136 , p_error_table => x_error_tbl
1137 , x_error_table => x_error_tbl
1138 );
1139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1140
1141 END retrieve_Business_Views;
1142 --
1143 END BIS_VG_REPOSITORY_MEDIATOR;