[Home] [Help]
PACKAGE BODY: APPS.BIS_VG_DESC_FLEX
Source
1 PACKAGE BODY bis_vg_desc_flex AS
2 /* $Header: BISTDFXB.pls 120.1.12010000.2 2008/10/25 00:00:38 dbowles ship $ */
3
4 --- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
5 --- All rights reserved.
6 ---
7 --- FILENAME
8 ---
9 --- BISTDFXB.pls
10 ---
11 --- DESCRIPTION
12 ---
13 --- body of package which handles the descriptive flexfield tag
14 ---
15 --- NOTES
16 ---
17 --- HISTORY
18 ---
19 --- 29-JUL-98 Created
20 --- 19-MAR-99 Edited by WNASRALL@US for exception handling
21 --- 21-Apr-99 Edited by WNASRALL@US to correct parsing behavior
22 --- 10-NOV-00 Edited by WNASRALL@US to add new function generate_pruned_view
23 --- 11-DEC-01 Edited by DBOWLES Added dr driver comments.
24 ---
25 ---
26 G_PKG_NAME CONSTANT VARCHAR(30) := 'bis_vg_desc_flex';
27 --- ===================
28 --- PRIVATE FUNCTION
29 --- ===================
30 ---
31 FUNCTION to_boolean(value IN VARCHAR2) RETURN BOOLEAN
32 IS
33 rv BOOLEAN;
34 BEGIN
35 IF(value in ('Y', 'y')) THEN
36 rv := TRUE;
37 ELSE
38 rv := FALSE;
39 END IF;
40 RETURN rv;
41 END;
42
43 -- =====================
44 -- PRIVATE PROCEDURES
45 -- =====================
46 -- ========================================
47 -- Procedure Name: Get_contexts
48 -- returns the contexts in a flexfield ordered by the creation_date
49 -- ========================================
50 PROCEDURE get_contexts(flexfield IN FND_DFLEX.DFLEX_R,
51 contexts OUT FND_DFLEX.CONTEXTS_DR)
52 IS
53 CURSOR context_c IS
54 SELECT descriptive_flex_context_code, descriptive_flex_context_name,
55 description, global_flag, enabled_flag
56 FROM fnd_descr_flex_contexts_vl
57 WHERE application_id = flexfield.application_id
58 AND descriptive_flexfield_name = flexfield.flexfield_name
59 ORDER BY creation_date;
60
61 CURSOR context_c_with_hints IS
62 SELECT /*+ leading(fnd_descr_flex_contexts_vl.t fnd_descr_flex_contexts_vl.b)
63 use_nl(fnd_descr_flex_contexts_vl.b)
64 index(fnd_descr_flex_contexts_vl.t
65 FND_DESCR_FLEX_CONTEXTS_TL_U1)*/
66 descriptive_flex_context_code, descriptive_flex_context_name,
67 description, global_flag, enabled_flag
68 FROM fnd_descr_flex_contexts_vl
69 WHERE application_id = flexfield.application_id
70 AND descriptive_flexfield_name = flexfield.flexfield_name
71 ORDER BY creation_date;
72
73 CURSOR context_c_new IS
74 SELECT descriptive_flex_context_code, descriptive_flex_context_name,
75 description, global_flag, enabled_flag
76 FROM fnd_descr_flex_contexts_vl
77 WHERE application_id = flexfield.application_id
78 AND descriptive_flexfield_name = flexfield.flexfield_name
79 ORDER BY creation_date, descriptive_flex_context_code ;
80
81 i BINARY_INTEGER := 0;
82 rv FND_DFLEX.CONTEXTS_DR;
83
84 BEGIN
85 rv.global_context := 0;
86 -- Bug 6819715
87 IF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = '9.2' THEN
88 FOR context_rec IN context_c_with_hints LOOP
89 i := i + 1;
90 rv.context_code(i) := context_rec.descriptive_flex_context_code;
91 rv.context_name(i) := context_rec.descriptive_flex_context_name;
92 rv.context_description(i) := context_rec.description;
93 rv.is_global(i) := to_boolean(context_rec.global_flag);
94 rv.is_enabled(i) := to_boolean(context_rec.enabled_flag);
95 IF(rv.is_global(i) AND rv.is_enabled(i)) THEN
96 rv.global_context := i;
97 END IF;
98 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
99 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
100 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
101 'context code is '||rv.context_code(i));
102 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
103 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
104 'context name is '||rv.context_name(i));
105 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
106 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
107 'context description is '||rv.context_description(i));
108 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
109 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
110 'is_enabled is '||context_rec.enabled_flag);
111 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
112 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
113 'is global is '||context_rec.global_flag);
114 end if;
115 END LOOP;
116 ELSIF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = '9i' THEN
117 FOR context_rec IN context_c LOOP
118 i := i + 1;
119 rv.context_code(i) := context_rec.descriptive_flex_context_code;
120 rv.context_name(i) := context_rec.descriptive_flex_context_name;
121 rv.context_description(i) := context_rec.description;
122 rv.is_global(i) := to_boolean(context_rec.global_flag);
123 rv.is_enabled(i) := to_boolean(context_rec.enabled_flag);
124 IF(rv.is_global(i) AND rv.is_enabled(i)) THEN
125 rv.global_context := i;
126 END IF;
127 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
128 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
129 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
130 'context code is '||rv.context_code(i));
131 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
132 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
133 'context name is '||rv.context_name(i));
134 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
135 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
136 'context description is '||rv.context_description(i));
137 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
138 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
139 'is_enabled is '||context_rec.enabled_flag);
140 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
141 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
142 'is global is '||context_rec.global_flag);
143 end if;
144 END LOOP;
145 ELSIF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = 'NEW' THEN
146 FOR context_rec IN context_c_new LOOP
147 i := i + 1;
148 rv.context_code(i) := context_rec.descriptive_flex_context_code;
149 rv.context_name(i) := context_rec.descriptive_flex_context_name;
150 rv.context_description(i) := context_rec.description;
151 rv.is_global(i) := to_boolean(context_rec.global_flag);
152 rv.is_enabled(i) := to_boolean(context_rec.enabled_flag);
153 IF(rv.is_global(i) AND rv.is_enabled(i)) THEN
154 rv.global_context := i;
155 END IF;
156 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
158 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
159 'context code is '||rv.context_code(i));
160 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
161 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
162 'context name is '||rv.context_name(i));
163 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
164 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
165 'context description is '||rv.context_description(i));
166 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
167 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
168 'is_enabled is '||context_rec.enabled_flag);
169 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
170 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
171 'is global is '||context_rec.global_flag);
172 end if;
173 END LOOP;
174 END IF;
175 rv.ncontexts := i;
176 contexts := rv;
177 END;
178
179
180
181 --
182 -- =============================================================================
183 -- PROCEDURE : parse_DF_Column_Line
184 -- PARAMETERS: 1. p_View_Column_Table table of varchars to hold columns of
185 -- view text
186 -- 2. p_Column_Pointer pointer to the key flex column in
187 -- column table (IN)
188 -- 3. x_Column_Pointer pointer to the char after the
189 -- delimiter in
190 -- column table (OUT)
191 -- 4. x_prefix prefix of descriptive flexfield, if any
192 -- 5. x_return_status error or normal
193 -- 6. x_error_Tbl table of error messages
194 -- COMMENT : Call this procedure to parse the KF view column tag.
195 -- ---
196 --=============================================================================
197 PROCEDURE parse_DF_Column_Line
198 ( p_View_Column_Table IN bis_vg_types.View_Text_Table_Type
199 , p_Column_Pointer IN bis_vg_types.View_Character_Pointer_Type
200 , x_Column_Pointer OUT bis_vg_types.View_Character_Pointer_Type
201 , x_EDW_flag OUT BOOLEAN --EDW flag change
202 , x_prefix OUT VARCHAR2
203 , x_decode OUT BOOLEAN
204 , x_return_status OUT VARCHAR2
205 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
206 )
207 IS
208 --
209 l_token VARCHAR2(100);
210 l_string bis_vg_types.view_text_table_rec_type;
211 l_pos NUMBER;
212 --
213 BEGIN
214
215 bis_debug_pub.Add('> parse_DF_Column_Line');
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217 -- get row of text from table
218 l_string := bis_vg_util.get_row ( p_View_Column_Table
219 , p_Column_Pointer
220 , x_return_status
221 , x_error_Tbl
222 );
223
224 -- get "_DF"
225 x_EDW_flag := FALSE; --EDW flag change
226 l_token := bis_vg_parser.get_string_token
227 ( l_string
228 , 1
229 , ':'
230 , l_pos
231 , x_return_status
232 , x_error_Tbl
233 );
234 bis_debug_pub.Add('l_token = ' || l_token);
235
236 IF(l_pos IS NOT NULL) THEN --- first real token exists
237 -- get next token, if any
238 x_prefix := bis_vg_parser.get_string_token
239 ( l_string
240 , l_pos
241 , ':'
242 , l_pos
243 , x_return_status
244 , x_error_Tbl
245 );
246
247 bis_debug_pub.Add('x_prefix = ' || x_prefix);
248
249 --EDW flag change
250
251 IF(upper(substr(x_prefix, 1, 4)) = '_EDW')
252 --- '_EDW' in forst position
253 THEN
254 bis_vg_util.add_message
255 ( DFX_COL_TAG_EXP_BAD_FLAG_MSG
256 , FND_MSG_PUB.G_MSG_LVL_ERROR
257 , 'MESSAGE_TAG'
258 , l_string
259 , x_return_status
260 , x_error_Tbl
261 );
262 --
263 RAISE MALFORMED_DFX_COL_TAG_BAD_FLAG;
264 --
265 END IF; --- '_EDW' in first position
266
267 IF(l_pos IS NOT NULL) --- Second token exists
268 THEN
269 l_token := bis_vg_parser.get_string_token
270 ( l_string
271 , l_pos
272 , ':'
273 , l_pos
274 , x_return_status
275 , x_error_Tbl
276 );
277
278 bis_debug_pub.Add('l_token = ' || l_token);
279
280
281 IF( UPPER(l_token) = '_EDW' )
282 THEN
283 x_EDW_flag := TRUE;
284 bis_debug_pub.Add('x_EDW_flag = TRUE');
285 ELSIF (UPPER(l_token) = '_BS' ) THEN
286 x_decode := TRUE;
287 bis_debug_pub.Add('x_decode = TRUE');
288 ELSE --- second token is not a valid flag
289 bis_vg_util.add_message
290 ( DFX_COL_TAG_EXP_BAD_FLAG_MSG
291 , FND_MSG_PUB.G_MSG_LVL_ERROR
292 , 'MESSAGE_TAG'
293 , l_string
294 , x_return_status
295 , x_error_Tbl
296 );
297 RAISE MALFORMED_DFX_COL_TAG_BAD_FLAG;
298 END IF; --- ( UPPER(l_token) = '_EDW' )
299
300 if (l_pos IS NOT NULL) THEN --- third token exists
301 l_token := bis_vg_parser.get_string_token
302 ( l_string
303 , l_pos
304 , ':'
305 , l_pos
306 , x_return_status
307 , x_error_Tbl
308 );
309 if ((upper(l_token) = '_BS') AND (x_decode = FALSE))
310 then
311 x_decode := TRUE;
312 elsif (( upper(l_token) = '_EDW') AND (x_EDW_flag = FALSE))
313 then
314 x_EDW_flag := TRUE;
315 bis_debug_pub.Add('x_EDW_flag = TRUE');
316 else --- Third token is not a valid flag
317 bis_vg_util.add_message
318 ( DFX_COL_TAG_EXP_BAD_FLAG_MSG
319 , FND_MSG_PUB.G_MSG_LVL_ERROR
320 , 'MESSAGE_TAG'
321 , l_string
322 , x_return_status
323 , x_error_Tbl
324 );
325 RAISE MALFORMED_DFX_COL_TAG_BAD_FLAG;
326
327 end if; --- third token = _BS or _EDW
328 end if; --- third token exists
329 ELSE --- second token does not exist (only one token)
330 if (x_prefix = '_BS') --- first and only token is '_BS'
331 THEN
332 x_decode := TRUE;
333 x_prefix := NULL;
334 END IF; --- first and only token is '_BS'
335 END IF; --- Second token exists
336 END IF; --- first token exists
337
338
339
340 x_Column_Pointer := bis_vg_util.increment_pointer_by_row
341 ( p_View_Column_Table
342 , p_Column_Pointer
343 , x_return_status
344 , x_error_Tbl
345 );
346 bis_debug_pub.Add('< parse_DF_Column_Line');
347 --
348
349 EXCEPTION
350 when FND_API.G_EXC_ERROR then
351 x_return_status := FND_API.G_RET_STS_ERROR ;
352 RAISE FND_API.G_EXC_ERROR;
353 when FND_API.G_EXC_UNEXPECTED_ERROR then
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 when others then
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 BIS_VG_UTIL.Add_Error_Message
359 ( p_error_msg_id => SQLCODE
360 , p_error_description => SQLERRM
361 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Column_Line'
362 , p_error_table => x_error_tbl
363 , x_error_table => x_error_tbl
364 );
365 bis_vg_log.update_failure_log( x_error_tbl
366 , x_return_status
367 , x_error_Tbl
368 );
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370
371 END parse_DF_Column_Line;
372 --
373 -- ============================================================================
374 -- FUNCTION: CHECK_APPLICATION_VALIDITY (PRIVATE FUNCTION)
375 -- RETURNS: boolean - true if application short name p[assed is defined
376 -- 1. p_app short name of application
377 --
378 -- COMMENT : Checks against the FND_APPLICATION_ALL_VIEW.
379 -- Called from parse_DF_Select_Line.
380 -- ---
381 -- ==========================================================================
382 FUNCTION CHECK_APPLICATION_VALIDITY
383 ( p_app IN VARCHAR2
384 )
385 return boolean
386 is
387 l_return_value boolean ;
388 l_dummy number;
389 cursor l_cursor is
390 select 1
391 from fnd_application_all_view
392 where application_short_name = p_app;
393 begin
394 BIS_DEBUG_PUB.Add('> check_application_validity');
395 open l_cursor ;
396 fetch l_cursor into l_dummy ;
397 l_return_value := l_cursor%found ;
398 close l_cursor ;
399 BIS_DEBUG_PUB.Add('< check_application_validity');
400 return(l_return_value);
401
402 END CHECK_APPLICATION_VALIDITY;
403
404
405 -- =============================================================================
406 -- PROCEDURE : parse_DF_Select_Line
407 -- PARAMETERS: 1. p_View_Select_Table table of varchars to hold select clause
408 -- of view text
409 -- 2. p_Select_Pointer pointer to the key flex column in select
410 -- table (IN)
411 -- 3. x_Select_Pointer pointer to the char after the delimiter in
412 -- select table (OUT)
413 -- 4. x_Application_Name Application Name
414 -- 5. x_Desc_Flex_Name Desc Flexfield name
415 -- 6. x_Table_Alias Table alias
416 -- 7. x_return_status error or normal
417 -- 8. x_error_Tbl table of error messages
418 -- COMMENT : Call this procedure to parse the DF selected tag.
419 -- ---
420 -- =============================================================================
421 PROCEDURE parse_DF_Select_Line
422 ( p_View_Select_Table IN bis_vg_types.View_Text_Table_Type
423 , p_Select_Pointer IN bis_vg_types.View_Character_Pointer_Type
424 , x_Select_Pointer OUT bis_vg_types.View_Character_Pointer_Type
425 , x_Application_Name OUT VARCHAR2
426 , x_Desc_Flex_Name OUT VARCHAR2
427 , x_Table_Alias OUT VARCHAR2
428 , x_DUMMY_flag OUT BOOLEAN
429 , x_return_status OUT VARCHAR2
430 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
431 )
432 IS
433 --
434 l_whole_tag VARCHAR2(2000);
435 l_tmp_pointer bis_vg_types.View_Character_Pointer_Type;
436 --
437 BEGIN
438 bis_debug_pub.Add('> parse_DF_Select_Line');
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440 -- get '_DF'
441 l_whole_tag := bis_vg_parser.get_token_increment_pointer
442 ( p_View_Select_Table
443 , p_Select_Pointer
444 , ':'''
445 , x_Select_Pointer
446 , x_return_status
447 , x_error_Tbl
448 );
449 --Parse the whole tag for error messages
450 l_whole_tag := bis_vg_parser.get_expression( p_View_Select_Table
451 , p_Select_Pointer
452 , l_tmp_pointer
453 , x_return_status
454 , x_error_Tbl
455 );
456 l_tmp_pointer := bis_vg_util.increment_pointer
457 ( p_View_Select_Table
458 , l_tmp_pointer
459 , x_return_status
460 , x_error_Tbl
461 );
462
463
464 IF bis_vg_util.equal_pointers(
465 l_tmp_pointer
466 , x_select_pointer
467 , x_return_status
468 , x_error_Tbl
469 )
470 THEN
471 BIS_VG_UTIL.Add_Error_message
472 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_APP_MSG
473 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
474 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
475 , p_token1 => 'tag'
476 , p_value1 => l_whole_tag
477 , p_error_table => x_error_tbl
478 , x_error_table => x_error_tbl
479 );
480 bis_vg_log.update_failure_log( x_error_tbl
481 , x_return_status
482 , x_error_Tbl
483 );
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486
487 x_Application_Name := bis_vg_parser.get_token_increment_pointer
488 ( p_View_Select_Table
489 , x_Select_Pointer
490 , ':'''
491 , x_Select_Pointer
492 , x_return_status
493 , x_error_Tbl
494 );
495
496 IF (x_Application_Name IS NULL
497 OR
498 bis_vg_util.equal_pointers(
499 l_tmp_pointer
500 , x_select_pointer
501 , x_return_status
502 , x_error_Tbl
503 )
504
505 ) THEN
506 BIS_VG_UTIL.Add_Error_message
507 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_APP_MSG
508 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
509 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
510 , p_token1 => 'tag'
511 , p_value1 => l_whole_tag
512 , p_error_table => x_error_tbl
513 , x_error_table => x_error_tbl
514 );
515 bis_vg_log.update_failure_log( x_error_tbl
516 , x_return_status
517 , x_error_Tbl
518 );
519 RAISE FND_API.G_EXC_ERROR;
520 END IF;
521
522 --EDW flag change
523
524 IF (x_Application_Name = '_DUMMY') THEN
525 x_DUMMY_flag := TRUE;
526
527 x_Application_Name:= bis_vg_parser.get_token_increment_pointer
528 ( p_View_Select_Table
529 , x_Select_Pointer
530 , ':'
531 , x_Select_Pointer
532 , x_return_status
533 , x_error_Tbl
534 );
535
536 END IF;
537 --EDW flag change
538
539 bis_debug_pub.Add('x_Application_Name = ' || x_Application_Name);
540 x_Desc_Flex_Name := bis_vg_parser.get_token_increment_pointer
541 ( p_View_Select_Table
542 , x_Select_Pointer
543 , ':'''
544 , x_Select_Pointer
545 , x_return_status
546 , x_error_Tbl
547 );
548
549 IF (x_desc_flex_name IS NULL
550 OR
551 bis_vg_util.equal_pointers(
552 l_tmp_pointer
553 , x_select_pointer
554 , x_return_status
555 , x_error_Tbl
556 )
557
558 ) THEN
559
560 BIS_VG_UTIL.Add_Error_message
561 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_NAME_MSG
562 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
563 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
564 , p_token1 => 'tag'
565 , p_value1 => l_whole_tag
566 , p_error_table => x_error_tbl
567 , x_error_table => x_error_tbl
568 );
569 bis_vg_log.update_failure_log( x_error_tbl
570 , x_return_status
571 , x_error_Tbl
572 );
573 RAISE FND_API.G_EXC_ERROR;
574
575 END IF;
576
577 bis_debug_pub.Add('x_Desc_Flex_Name = ' || x_Desc_Flex_Name);
578 x_Table_Alias := bis_vg_parser.get_token
579 ( p_View_Select_Table
580 , x_Select_Pointer
581 , ':'''
582 , x_Select_Pointer
583 , x_return_status
584 , x_error_Tbl
585 );
586 bis_debug_pub.Add('x_Table_Alias = ' || x_Table_Alias);
587
588
589 IF (x_table_alias IS NULL
590 OR
591 bis_vg_util.equal_pointers(
592 l_tmp_pointer
593 , x_select_pointer
594 , x_return_status
595 , x_error_Tbl
596 )
597
598 ) THEN
599 BIS_VG_UTIL.Add_Error_message
600 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_TABLE_MSG
601 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
602 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
603 , p_token1 => 'tag'
604 , p_value1 => l_whole_tag
605 , p_error_table => x_error_tbl
606 , x_error_table => x_error_tbl
607 );
608 bis_vg_log.update_failure_log( x_error_tbl
609 , x_return_status
610 , x_error_Tbl
611 );
612 RAISE FND_API.G_EXC_ERROR;
613 END IF;
614
615 IF check_application_validity(x_Application_Name)
616 THEN
617 -- EVERYTHING IS FINE
618 x_Select_Pointer := bis_vg_util.increment_pointer( p_View_Select_Table
619 , x_Select_Pointer
620 , x_return_status
621 , x_error_Tbl
622 );
623 bis_debug_pub.Add('< parse_DF_Select_Line');
624 ELSE
625 BIS_VG_UTIL.Add_Error_message
626 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_INVALID_APP
627 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
628 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
629 , p_token1 => 'tag'
630 , p_value1 => l_whole_tag
631 , p_token2 => 'app'
632 , p_value2 => x_Application_Name
633 , p_error_table => x_error_tbl
634 , x_error_table => x_error_tbl
635 );
636 bis_vg_log.update_failure_log( x_error_tbl
637 , x_return_status
638 , x_error_Tbl
639 );
640 RAISE FND_API.G_EXC_ERROR;
641
642 END IF;
643
644 --
645 EXCEPTION
646 when
647 FND_API.G_EXC_ERROR then
648 x_return_status := FND_API.G_RET_STS_ERROR ;
649 RAISE FND_API.G_EXC_ERROR;
650 when FND_API.G_EXC_UNEXPECTED_ERROR then
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
653 when others then
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
655 BIS_VG_UTIL.Add_Error_Message
656 ( p_error_msg_id => SQLCODE
657 , p_error_description => SQLERRM
658 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
659 , p_error_table => x_error_tbl
660 , x_error_table => x_error_tbl
661 );
662 bis_vg_log.update_failure_log( x_error_tbl
663 , x_return_status
664 , x_error_Tbl
665 );
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667
668 END parse_DF_Select_Line;
669 --
670 -- =============================================================================
671 -- PROCEDURE : add_desc_flexfield_segments
672 -- PARAMETERS: 1. p_nContexts_flag is TRUE if only 1 context has been defined
673 -- 2. p_Flexfield desc flexfield dflex_r variable
674 -- 3. p_Flexinfo desc flexfield dflex_dr variable
675 -- 4. p_Context_Code context of the context of the desc flexfield
676 -- 5. p_Context_Num number of the context of the desc flexfield
677 -- 6. p_Prefix Desc Flexfield Name
678 -- 7. p_Table_Alias Table alias
679 -- 8. x_Select_Table table of varchars to hold select clause of
680 -- view text
681 -- 9. x_Column_Comment_Table table to hold flex info
682 --
683 -- 10. x_Column_Table table of varchars to hold select clause of
684 -- view text
685 -- 11. p_attr_categ_flag flag to indicate if ATTRIBUTE_CATEGORY is
686 -- to be added
687 -- 12. x_attr_categ_flag updated flag to indicate if
688 -- ATTRIBUTE_CATEGORY is to be added
689 -- 13. x_return_status error or normal
690 -- 14. x_error_Tbl table of error messages
691 -- 15. p_schema schema name -- schema name
692 -- COMMENT : Call this procedure to add the segments for desc flexfields.
693 -- ---
694 -- ============================================================================
695 PROCEDURE add_desc_flexfield_segments
696 ( p_nContexts_flag IN BOOLEAN
697 , p_Flexfield IN FND_DFLEX.DFLEX_R
698 , p_Flexinfo IN FND_DFLEX.DFLEX_DR
699 , p_Context_Code IN
700 FND_DESCR_FLEX_CONTEXTS.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE
701 , p_Context_Num IN NUMBER
702 , p_Prefix IN VARCHAR2
703 , p_decode IN BOOLEAN
704 , p_Table_Alias IN VARCHAR2
705 , p_EDW_Flag IN BOOLEAN --EDW flag change
706 , p_DUMMY_Flag IN BOOLEAN --EDW flag change
707 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
708 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
709 , x_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
710 , p_attr_categ_flag IN BOOLEAN
711 , x_attr_categ_flag OUT BOOLEAN
712 , x_return_status OUT VARCHAR2
713 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
714 , p_schema IN VARCHAR2 --schema name
715 )
716 IS
717 --
718 l_segments FND_DFLEX.SEGMENTS_DR;
719 --
720 l_prefix VARCHAR(100);
721 l_count NUMBER;
722 l_context_code VARCHAR2(100) := NULL;
723 l_col_data_type varchar2(106) :=null;
724 type CurType is ref cursor;
725 cv CurType;
726 --
727 BEGIN
728 bis_debug_pub.Add('> add_desc_flexfield_segments');
729 x_return_status := FND_API.G_RET_STS_SUCCESS;
730 IF(p_Prefix IS NOT NULL) THEN
731 l_prefix := p_Prefix || '_';
732 END IF;
733
734 bis_debug_pub.Add('l_prefix = ' || l_prefix);
735
736 x_attr_categ_flag := p_attr_categ_flag;
737 IF(p_Context_Code IS NOT NULL AND p_nContexts_flag = FALSE) THEN
738 -- l_context_code := '^' || p_Context_Code;
739 l_context_code := '^' || TO_CHAR(p_Context_Num);
740 END IF;
741 --
742 FND_DFLEX.GET_SEGMENTS
743 ( context => FND_DFLEX.MAKE_CONTEXT
744 ( flexfield => p_flexfield
745 , context_code => p_Context_Code
746 )
747 , segments => l_segments
748 , enabled_only => TRUE
749 );
750 IF(l_segments.NSEGMENTS > 0) THEN
751 IF(p_attr_categ_flag = TRUE) THEN
752 ---EDW flag change
753 IF (p_EDW_Flag = TRUE) THEN
754 x_Column_Table(1) := l_prefix || 'context';
755 x_Column_Table(1) := l_prefix || 'context';
756 x_Column_Comment_Table(1).column_name:= x_Column_Table(1);
757 x_Column_Comment_Table(1).flex_type := 'DESC CONTEXT';
758 --populate comments with application id, flex name, context code
759 x_Column_Comment_Table(1).column_comments :=p_flexfield.application_id||','||
760 p_flexfield.flexfield_name||','||
761 p_context_code;
762
763 ELSE
764 x_Column_Table(1) := l_prefix
765 || p_Flexinfo.form_context_prompt;
766 x_Column_Comment_Table(1).column_name:= x_Column_Table(1);
767 x_Column_Comment_Table(1).flex_type := 'DESC CONTEXT';
768 --populate comments with application id, flex name, context code
769 x_Column_Comment_Table(1).column_comments :=p_flexfield.application_id||','||
770 p_flexfield.flexfield_name||','||
771 p_context_code;
772 END IF;
773 ---EDW flag change
774
775 bis_debug_pub.Add('x_Column_Table(1) = ' || x_Column_Table(1));
776
777 -- x_Select_Table(1) := ' ' || p_Table_Alias || '.'
778 -- || p_Flexinfo.context_column_name;
779 --EDW flag change
780 IF p_DUMMY_flag
781 THEN
782 x_Select_Table(1) := 'TO_CHAR(NULL)';
783 ELSE
784 x_Select_Table(1) := ' ' || p_Table_Alias ||
785 '.' || p_Flexinfo.context_column_name;
786 END IF;
787 --EDW flag change
788
789 x_attr_categ_flag := FALSE;
790 END IF;
791 l_count := x_Column_Table.COUNT + 1;
792 FOR j IN 1 .. l_segments.NSEGMENTS LOOP
793 --
794 x_Column_Table(l_count) := l_prefix
795 || l_segments.SEGMENT_NAME(j)
796 || l_context_code;
797 x_Column_Comment_Table(l_count).column_name:= x_Column_Table(l_count);
798 x_Column_Comment_Table(l_count).flex_type := 'DESC SEGMENT';
799 --populate comments with application id, flex name, context code, application column
800 x_Column_Comment_Table(l_count).column_comments :=p_flexfield.application_id||','||
801 p_flexfield.flexfield_name||','||
802 p_context_code||','||
803 l_segments.APPLICATION_COLUMN_NAME(j);
804 bis_debug_pub.Add('x_Column_Table('|| l_count||') = '
805 || x_Column_Table(l_count));
806 IF (p_decode = TRUE) THEN
807 x_Select_Table(l_count) := ', '
808 || 'DECODE( '
809 ||p_flexinfo.context_column_name
810 || ', '
811 || ''''
812 ||p_context_code
813 ||''''
814 || ','
815 || p_Table_Alias
816 || '.'
817 || l_segments.APPLICATION_COLUMN_NAME(j)
818 || ', NULL'
819 || ')';
820 ELSE
821 --EDW flag change
822 IF p_DUMMY_flag
823 THEN
824 if l_segments.APPLICATION_COLUMN_NAME(j) not like 'ATTRIBUTE%' then
825 --use schema
826 open cv for
827 select DATA_TYPE
828 from all_tab_columns
829 where table_name = p_flexinfo.table_name
830 and column_name=l_segments.APPLICATION_COLUMN_NAME(j)
831 and owner =p_schema;
832
833 fetch cv into l_col_data_type;
834 close cv;
835 if l_col_data_type like 'NUMBER%'
836 THEN
837 x_Select_Table(l_count) := ', TO_NUMBER(NULL)';
838 ELSIF l_col_data_type like 'DATE%' THEN
839 x_Select_Table(l_count) := ', TO_DATE(NULL)';
840 ELSE
841 x_Select_Table(l_count) := ', TO_CHAR(NULL)';
842 end if;
843 ELSE
844 x_Select_Table(l_count) := ', TO_CHAR(NULL)';
845 end if;
846 ELSE
847 x_Select_Table(l_count) := ', ' || p_Table_Alias || '.'
848 || l_segments.APPLICATION_COLUMN_NAME(j);
849 END IF;
850 --EDW flag change
851 END IF;
852 bis_debug_pub.Add('x_Select_Table('|| l_count||') = ' || x_Select_Table(l_count));
853 --
854 l_count := l_count + 1;
855 END LOOP;
856 END IF;
857 bis_debug_pub.Add('< add_desc_flexfield_segments');
858 --
859 EXCEPTION
860 when FND_API.G_EXC_ERROR then
861 x_return_status := FND_API.G_RET_STS_ERROR ;
862 RAISE FND_API.G_EXC_ERROR;
863 when FND_API.G_EXC_UNEXPECTED_ERROR then
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
866 when others then
867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
868 BIS_VG_UTIL.Add_Error_Message
869 ( p_error_msg_id => SQLCODE
870 , p_error_description => SQLERRM
871 , p_error_proc_name => G_PKG_NAME||'.add_desc_flexfield_segments'
872 , p_error_table => x_error_tbl
873 , x_error_table => x_error_tbl
874 );
875 bis_vg_log.update_failure_log( x_error_tbl
876 , x_return_status
877 , x_error_Tbl
878 );
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880
881 END add_desc_flexfield_segments;
882 --
883 -- =============================================================================
884 -- PROCEDURE : update_Desc_Flex_Tables
885 -- PARAMETERS:
886 -- 1. p_EDW_Flag flag to add context column
887 -- 2. p_dummy_flag flag to indicate a flexfield
888 -- which is not valid in this branch
889 -- of a union, hence filled with
890 -- NULLs to keep number of columns.
891 -- 3. p_Prefix prefix for segments
892 -- 4. p_decode flag to indicate if select
893 -- statement should contain a decode
894 -- or always fetch data even when
895 -- meaningless
896 -- 5. p_column_table PLSQL table of columns to prune by
897 -- if present, else expand all.
898 -- 6. p_Application_Name Application Name
899 -- 7. p_Desc_Flex_Name Desc Flexfield Name
900 -- 8. p_Table_Alias Table alias
901 -- 9. x_Column_Table table of varchars to hold
902 -- view columns
903 -- 10. x_Select_Table table of varchars to hold select
904 -- clause of view text
905 -- 10. x_Select_Table table of varchars to hold select
906 -- clause of view text
907 -- 11. x_Column_Comment_Table table to hold flex info as it is gathered
908 -- 12. x_return_status error or normal (not used)
909 -- 13. x_error_Tbl table of error messages
910
911 -- COMMENT : Call this procedure to build the column and select tables
912 -- for desc flexfields.
913 -- ---
914 -- =============================================================================
915 PROCEDURE update_Desc_Flex_Tables
916 ( p_EDW_Flag IN BOOLEAN --EDW flag change
917 , p_DUMMY_Flag IN BOOLEAN --EDW flag change
918 , p_Prefix IN VARCHAR2
919 , p_decode IN BOOLEAN
920 , p_column_table IN BIS_VG_TYPES.flexfield_column_table_type
921 , p_Application_Name IN VARCHAR2
922 , p_Desc_Flex_Name IN VARCHAR2
923 , p_Table_Alias IN VARCHAR2
924 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
925 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
926 , x_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
927 , x_return_status OUT VARCHAR2
928 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
929 )
930 IS
931 --
932 i NUMBER;
933 l_flexfield FND_DFLEX.DFLEX_R;
934 l_flexinfo FND_DFLEX.DFLEX_DR;
935 l_contexts FND_DFLEX.CONTEXTS_DR;
936 l_segments FND_DFLEX.SEGMENTS_DR;
937 --
938 l_ATT_CATEGORY_flag BOOLEAN := TRUE;
939 l_nContexts_flag BOOLEAN;
940 l_count NUMBER := 0;
941 l_Column_Table bis_vg_types.View_Text_Table_Type;
942 l_Select_Table bis_vg_types.View_Text_Table_Type;
943 l_Column_Comment_Table bis_vg_types.Flex_Column_Comment_Table_Type;
944 ---
945
946 --to get schema name
947 l_dummy1 varchar2(2000);
948 l_dummy2 varchar2(2000);
949 l_schema varchar2(400);
950
951 BEGIN
952 --- bis_debug_pub.debug_on;
953 bis_debug_pub.Add('> update_Desc_Flex_Tables');
954
955 --get schema name
956 if FND_INSTALLATION.GET_APP_INFO(p_Application_Name,l_dummy1, l_dummy2,l_schema) = false then
957 bis_debug_pub.Add('FND_INSTALLATION.GET_APP_INFO returned with error');
958 end if;
959
960 x_return_status := FND_API.G_RET_STS_SUCCESS;
961 FND_DFLEX.GET_FLEXFIELD( appl_short_name => p_Application_Name
962 , flexfield_name => p_Desc_Flex_Name
963 , flexfield => l_flexfield
964 , flexinfo => l_flexinfo
965 );
966 bis_debug_pub.Add('l_flexfield.FLEXFIELD_NAME = '
967 || l_flexfield.FLEXFIELD_NAME);
968 bis_debug_pub.Add('l_flexinfo.TITLE = ' || l_flexinfo.TITLE);
969 IF p_column_table IS NULL
970 --- regular BVG behavior (not pruned)
971 THEN
972 GET_CONTEXTS
973 ( flexfield => l_flexfield
974 , contexts => l_contexts
975 );
976 bis_debug_pub.Add('l_contexts.NCONTEXTS = ' || l_contexts.NCONTEXTS);
977 -- set l_nContexts_flag if only one context or only one enabled context
978 IF(l_contexts.NCONTEXTS = 1) THEN
979 l_nContexts_flag := TRUE;
980 ELSE
981 FOR i IN 1 .. l_contexts.NCONTEXTS LOOP
982 IF( l_contexts.IS_ENABLED(i) ) THEN
983 l_count := l_count + 1;
984 END IF;
985 END LOOP;
986 IF(l_count > 1) THEN
987 l_nContexts_flag := FALSE;
988 ELSE
989 if (p_edw_flag = TRUE ) THEN
990 l_nContexts_flag := FALSE;
991 else
992 l_nContexts_flag := TRUE;
993 end if;
994 END IF;
995 END IF;
996 --
997 l_count:=1;
998 FOR i IN 1 .. l_contexts.NCONTEXTS LOOP
999 IF( l_contexts.IS_ENABLED(i) ) THEN
1000 --EDW flag change
1001 IF (p_EDW_Flag = TRUE)
1002 THEN
1003 l_count := i+1;
1004 ELSE
1005 l_count := i;
1006 END IF;
1007 --EDW flag change
1008 add_desc_flexfield_segments( l_nContexts_flag
1009 , l_flexfield
1010 , l_flexinfo
1011 , l_contexts.CONTEXT_CODE(i)
1012 , l_count
1013 , p_Prefix
1014 , p_decode
1015 , p_Table_Alias
1016 , p_EDW_flag
1017 , p_DUMMY_flag
1018 , l_Column_Table
1019 , l_Select_Table
1020 , l_Column_Comment_Table
1021 , l_ATT_CATEGORY_flag
1022 , l_ATT_CATEGORY_flag
1023 , x_return_status
1024 , x_error_Tbl
1025 ,l_schema --pass schema name
1026
1027 );
1028
1029 --- Append the latest context's segments to the list
1030 bis_vg_util.concatenate_Tables( x_Column_Table
1031 , l_Column_Table
1032 , x_Column_Table
1033 , x_return_status
1034 , x_error_Tbl
1035 );
1036 bis_vg_util.concatenate_Tables( x_Select_Table
1037 , l_Select_Table
1038 , x_Select_Table
1039 , x_return_status
1040 , x_error_Tbl
1041 );
1042 bis_vg_util.concatenate_Tables( x_Column_Comment_Table
1043 , l_Column_Comment_Table
1044 , x_Column_Comment_Table
1045 , x_return_status
1046 , x_error_Tbl
1047 );
1048 END IF; --- Context enabled
1049 END LOOP;
1050 ELSE
1051 --- The pruned case - no need for concatenated segments column.
1052 l_count := 1;
1053 i := p_column_table.first;
1054 WHILE i <= p_column_table.last
1055 LOOP
1056 IF ( p_column_table(i).flex_field_type = 'D'
1057 AND p_column_table(i).id_flex_code = p_desc_flex_name)
1058 THEN
1059 l_count := l_count+1;
1060 bis_debug_pub.ADD ('Processing p_column_table(i).segment_name = '
1061 || p_column_table(i).segment_name);
1062 bis_debug_pub.ADD ('and p_column_table(i).structure_num = '
1063 || p_column_table(i).structure_num);
1064 bis_debug_pub.ADD ('and p_column_table(i).application_column_name = '
1065 || p_column_table(i).application_column_name);
1066 x_column_table(l_count):= p_Prefix
1067 || '_' || p_column_table(i).segment_name;
1068
1069 IF (p_column_table(i).structure_num > 0)
1070 THEN
1071 x_column_table(l_count):= x_column_table(l_count)
1072 ||'^' ||p_column_table(i).structure_num;
1073 END IF;
1074
1075 IF p_dummy_flag OR p_column_table(i).application_column_name IS NULL
1076 THEN
1077 IF p_column_table(i).segment_datatype = 'N'
1078 THEN
1079 x_select_table(l_count) := ', TO_NUMBER(NULL)';
1080 ELSIF p_column_table(i).segment_datatype IN ('D','X')
1081 THEN
1082 x_select_table(l_count) := ', TO_DATE(NULL)';
1083 ELSE
1084 x_select_table(l_count) := ', TO_CHAR(NULL)';
1085 END IF;
1086 ELSE
1087 x_select_table(l_count)
1088 := ' , ' || p_Table_Alias || '.'
1089 || p_column_table(i).application_column_name;
1090 END IF; --- dummy_flag
1091
1092 END IF; --- p_column_table(i) matches criteria
1093 i := p_column_table.next(i);
1094 END LOOP; --- to enumerate p_column_table
1095
1096 IF l_count > 1
1097 THEN
1098 x_Column_Table(1) := p_prefix || '_context';
1099 bis_debug_pub.ADD ('l_Flexinfo.context_column_name = '
1100 || l_Flexinfo.context_column_name);
1101 IF p_dummy_flag OR l_Flexinfo.context_column_name IS NULL
1102 THEN
1103 x_Select_Table(1) := 'NULL';
1104 ELSE
1105 x_Select_Table(1) := ' '
1106 || p_Table_Alias
1107 || '.' || l_Flexinfo.context_column_name;
1108 END IF;
1109
1110 END IF;
1111
1112 END IF; --- Prune or no prune
1113 --- DEBUG
1114 bis_vg_util.print_View_Text
1115 ( x_Column_Table
1116 , x_return_status
1117 , x_error_Tbl
1118 );
1119 bis_debug_pub.Add('< update_Desc_Flex_Tables');
1120 --- bis_debug_pub.debug_off;
1121 --
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||'.update_Desc_Flex_Tables'
1136 , p_error_table => x_error_tbl
1137 , x_error_table => x_error_tbl
1138 );
1139 bis_vg_log.update_failure_log( x_error_tbl
1140 , x_return_status
1141 , x_error_Tbl
1142 );
1143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1144
1145
1146 END update_Desc_Flex_Tables;
1147 --
1148 --- ============================================================================
1149 --- PROCEDURE : add_Desc_Flex_Info
1150 --- PARAMETERS:
1151 --- 1. p_View_Column_Table table of varchars to hold columns of
1152 --- view text
1153 --- 2. p_View_Select_Table table of varchars to hold select clause
1154 --- of view
1155 --- 3. p_Mode mode of execution of the program
1156 --- 4. p_column_table List of columns for calls from generate_pruned_view
1157 --- 5. p_Column_Pointer pointer to the desc flex column in
1158 --- column table
1159 --- 6. p_Select_Pointer pointer to the select clause
1160 --- 6. p_From_Pointer pointer to the corresponding from clause
1161 --- 8. x_Column_Table table of varchars to hold additional
1162 --- columns
1163 --- 9. x_Select_Table table of varchars to hold additional
1164 --- columns
1165 --- 10 x_Column_Comment_Table table to hold info info as it is gathered.
1166 --- 11. x_Column_Pointer pointer to the character after the
1167 --- delimiter
1168 --- (column table)
1169 --- 12. x_Select_Pointer pointer to the character after the
1170 --- delimiter
1171 --- (select table)
1172 --- 14. x_return_status error or normal
1173 --- 15. x_error_Tbl table of error messages
1174 ---
1175 --- COMMENT : Call this procedure to add a particular desc flexfield
1176 --- information to a view.
1177 --- ---
1178 --- ==========================================================================
1179
1180 PROCEDURE add_Desc_Flex_Info
1181 ( p_View_Column_Table IN BIS_VG_TYPES.View_Text_Table_Type
1182 , p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
1183 , p_Mode IN NUMBER
1184 , p_column_table IN BIS_VG_TYPES.flexfield_column_table_type
1185 , p_Column_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
1186 , p_Select_Pointer IN bis_vg_types.View_Character_Pointer_Type
1187 , p_From_Pointer IN bis_vg_types.View_Character_Pointer_Type
1188 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
1189 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
1190 , x_Column_Comment_Table OUT BIS_VG_TYPES.Flex_Column_Comment_Table_Type
1191 , x_Column_Pointer OUT bis_vg_types.View_Character_Pointer_Type
1192 , x_Select_Pointer OUT bis_vg_types.View_Character_Pointer_Type
1193 , x_return_status OUT VARCHAR2
1194 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1195 )
1196 IS
1197 --
1198 l_Prefix VARCHAR2(100);
1199 --
1200 l_Application_Name VARCHAR2(10);
1201 l_Desc_Flex_Name VARCHAR2(100);
1202 l_Table_Alias VARCHAR2(100);
1203 l_Table_Name VARCHAR2(100);
1204 --
1205 l_decode BOOLEAN;
1206 l_EDW_Flag BOOLEAN; --EDW flag change
1207 l_DUMMY_Flag BOOLEAN; --EDW flag change
1208 BEGIN
1209 bis_debug_pub.Add('> add_Desc_Flex_Info');
1210 x_return_status := FND_API.G_RET_STS_SUCCESS;
1211
1212 parse_DF_Column_Line( p_View_Column_Table
1213 , p_Column_Pointer
1214 , x_Column_Pointer
1215 , l_EDW_Flag --EDW flag change
1216 , l_Prefix
1217 , l_decode
1218 , x_return_status
1219 , x_error_Tbl
1220 );
1221
1222 bis_debug_pub.Add('l_Prefix = ' || l_Prefix);
1223
1224 --- This clause catches flexfield tags that do not have the
1225 --- _EDW tags when the generator is called via generate_pruned_view
1226 IF (p_column_table IS NOT NULL AND l_edw_flag = FALSE )
1227 THEN
1228 RAISE bis_view_generator_pvt.CANNOT_PRUNE_NON_EDW_VIEW;
1229 END IF;
1230
1231
1232 parse_DF_Select_Line( p_View_Select_Table
1233 , p_Select_Pointer
1234 , x_Select_Pointer
1235 , l_Application_Name
1236 , l_Desc_Flex_Name
1237 , l_Table_Alias
1238 , l_DUMMY_Flag --EDW flag change
1239 , x_return_status
1240 , x_error_Tbl
1241 );
1242 bis_debug_pub.Add('l_Application_Name = ' || l_Application_Name);
1243 bis_debug_pub.Add('l_Desc_Flex_Name = ' || l_Desc_Flex_Name);
1244 bis_debug_pub.Add('l_Table_Alias = ' || l_Table_Alias);
1245 bis_debug_pub.Add('l_Prefix = ' || l_Prefix);
1246
1247 IF(
1248 (p_Mode <> bis_vg_types.remove_tags_mode)
1249 AND
1250 (p_column_table IS NULL
1251 OR
1252 p_column_table.COUNT > 0)
1253 )
1254
1255 THEN
1256 --- x_Column_Table(1) := 'DESCRIPTIVE_FLEXFIELD_COLUMN';
1257 --- x_Select_Table(1) := 'TO_CHAR(NULL)';
1258 --- ELSE
1259
1260 update_Desc_Flex_Tables( l_EDW_Flag --EDW flag change
1261 , l_DUMMY_Flag --EDW flag change
1262 , l_Prefix
1263 , l_decode
1264 , p_column_table
1265 , l_Application_Name
1266 , l_Desc_Flex_Name
1267 , l_Table_Alias
1268 , x_Column_Table
1269 , x_Select_Table
1270 , x_Column_Comment_Table
1271 , x_return_status
1272 , x_error_Tbl
1273 );
1274 END IF;
1275 bis_vg_util.print_View_Text(x_Column_Table, x_return_status, x_error_Tbl);
1276 bis_vg_util.print_View_Text(x_Select_Table, x_return_status, x_error_Tbl);
1277 bis_debug_pub.Add('COLUMN POINTER');
1278 bis_vg_util.print_View_Pointer ( x_Column_Pointer
1279 , x_return_status
1280 , x_error_Tbl
1281 );
1282 bis_debug_pub.Add('SELECT POINTER');
1283 bis_vg_util.print_View_Pointer ( x_Select_Pointer
1284 , x_return_status
1285 , x_error_Tbl
1286 );
1287 bis_debug_pub.Add('< add_Desc_Flex_Info');
1288
1289 --
1290
1291
1292 EXCEPTION
1293 when bis_view_generator_pvt.cannot_prune_non_edw_view THEN
1294 RAISE; -- same exception
1295 when FND_API.G_EXC_ERROR then
1296 x_return_status := FND_API.G_RET_STS_ERROR ;
1297 RAISE; -- same exception
1298 when FND_API.G_EXC_UNEXPECTED_ERROR then
1299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1300 RAISE; -- same exception
1301 when others then
1302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1303 BIS_VG_UTIL.Add_Error_Message
1304 ( p_error_msg_id => SQLCODE
1305 , p_error_description => SQLERRM
1306 , p_error_proc_name => G_PKG_NAME||'.add_Desc_Flex_Info'
1307 , p_error_table => x_error_tbl
1308 , x_error_table => x_error_tbl
1309 );
1310 bis_vg_log.update_failure_log( x_error_tbl
1311 , x_return_status
1312 , x_error_Tbl
1313 );
1314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1315
1316
1317 END add_Desc_Flex_Info;
1318 --
1319 --
1320 END bis_vg_desc_flex;